Date cell is not calculating correctly

04/24/19 Edited 12/09/19

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

Tags:

Comments

  • Here is my actual formula

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

    Also tried....

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

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul 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.

    thinkspi.com

  • L_123L_123 ✭✭✭✭✭
    edited 04/24/19

    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 NewcomePaul Newcome ✭✭✭✭✭
    edited 04/24/19

    [email protected]:

     

    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.

    thinkspi.com

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • L_123L_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 NewcomePaul 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.

    thinkspi.com

Sign In or Register to comment.