Excel date formula doesn't work past the new year

PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 29, 2017 at 01:45 PM - Latest reply: PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen
- Dec 30, 2017 at 11:36 AM
Hello,

I have a problem with a date formula in a spreadsheet that I would very much appreciate help with. It seems that the formula doesn't work past the new year for some reason. My formula is set as follows:

Cell Z3 has a date on it, cell AA3 should populate with a future date 4 weeks from now. My formula is =IF(Z3="","",Z3+7*4). This works as long as the future date doesn't go past the new year. If the date is for 2018, it gives #VALUE as a result.

The spreadsheet is in Excel Online (O365).

Thank you in advance for your help!

Kind regards,

Paivi
See more 

Your reply

5 replies

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Dec 29, 2017 at 02:26 PM
0
Thank you
Paivi, Good afternoon.

Analyzing your situation, the result is very strange.
The formula is correct.

I suppose the cells Z3 and AA3 are formatted as DATE.
I also suppose that when entering the date you have typed as date and there being no possibility of entering as text.

Try doing the same procedure using other cells.

Example:
AC10 as the cell of the original date.
AD10 as the formula cell.

Format AC10 and AD10 as dates.

Enter the date.
Does the same problem happen ???

Do this and tell us the result.
We will be here and we will try to help you.
--
Belo Horizonte, Brasil.
Marcílio Lobão
PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 29, 2017 at 03:02 PM
Hi,

Thank you for your quick reply. Yes unfortunately, the problem is still there. I've checked that the formatting is definitely as date. Could it be because it's Excel online? I'm totally baffled by this, and worry I won't be able to give an answer to my client.

Kind regards,

Paivi
Respond to Mazzaropi
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Dec 29, 2017 at 03:18 PM
0
Thank you
Paivi,

"...Could it be because it's Excel online? ..."
I do not think it would make any sense if it happened.
It is a basic operation.
And you're using EXCEL, with the only difference being the online version.

Did you try to open a new worksheet and do the same test?

Here's the example I made for you using Excel 2007 (it's what I have).
https://www.sendspace.com/file/nm6mvf

I'll wait for your news.
--
Belo Horizonte, Brasil.
Marcílio Lobão
ac3mark 9557 Posts Monday June 3, 2013Registration dateModeratorStatus June 18, 2018 Last seen - Dec 29, 2017 at 03:41 PM
"try to open a new worksheet and do the same test"

The above has gotten me through a couple of times!
PaiviM 8 Posts Friday December 1, 2017Registration date December 30, 2017 Last seen - Dec 30, 2017 at 11:36 AM
Hi,

I've downloaded the spreadsheet as it's stored in O365 as a shared document. The formula on the download works fine and gives the dates for 2018. I've created the formulas on the shared document and have access to update it on the browser (allows to make quick changes) but not on the "edit in Excel" option which would give full functionality of the spreadsheet. They are migrating from google docs and that stops me from accessing the "edit in excel" as I'm an outside contractor and work from home.

Kind regards,

Paivi
Respond to Mazzaropi