Date cell is not calculating correctly

jsteele86441

I have a sheet where I want to add 30-days to another cell that is a date. 

I am using the formula:  =(original date cell) + 30 

Example:  The original cell has a date of 3/19/19.  I should get 4/18/19. 

However, I'm getting 3/19/1930.

I have cleared the cell contents, deleted the row, and whatever else I can to create a clean cell format.  What's up??

jsteele86441

Here is my actual formula

=([Ext.#1 Due Date]1 + 30)

Also tried....

=([Ext.#1 Due Date]1)+30

Andrée Starå

Hi,

Have you made sure they both are Date column types?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I agree with Andree. I've a feeling it's going to be the column holding the formula that is set to a text/number type.

L_123

I attempted to duplicate this error and was unable to. That said you could try using a workaround.

 

=date(year([Ext.#1 Due Date]1,month([Ext.#1 Due Date]1),day([Ext.#1 Due Date]1)+30)

 

Beyond that, can you try something for me?

 

Put these formulas in a text/number column and post what the results are.

 

=day([Ext.#1 Due Date]1)

=month([Ext.#1 Due Date]1)

=year([Ext.#1 Due Date]1)

 

That might tell us why the data isn't calculating in the correct locations

Paul Newcome

L@123:

 

Your workaround will throw an #INVALID VALUE error for most of the dates. Adding 30 to the day portion of today's date 24 April 2019 would return a non-existent date of 54 April 2019.

Mike Wilday

I also agree with Paul and Andree. It sounds like your column type isn't set up as a date column. 

L_123

That's weird. I could have sworn that formula used to work. You're right though, smartsheet isn't recalculating the whole date when the days or months are over their highest amount.

Paul Newcome

I've only been in SS for about a year now, but I don't remember it working like that since I've been using it. I've had to write in a lot of IFERROR statements to compensate for that sort of thing as I work with A LOT of date calculations.