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??

SS Screen.png

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…