Date cell is not calculating correctly
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??
Comments
-
Here is my actual formula
=([Ext.#1 Due Date]1 + 30)
Also tried....
=([Ext.#1 Due Date]1)+30
-
Hi,
Have you made sure they both are Date column types?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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
-
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.
-
I also agree with Paul and Andree. It sounds like your column type isn't set up as a date column.
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!