Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Date calculation


How do I create a cell formula that calculates a future date from another cell that has a date?





  • Jeremy Michels
    Jeremy Michels ✭✭✭✭✭

    Is the date going to change in the other Sheet? Are you doing this in sheets or reports? 


    If the date doesn't change copy and paste and run formula off the date. If the date changes you need to link it to a cell. In the same sheet the Run off that cell. May need to add a column to your sheet. 


  • =[Source Data Cell] + [Desired Number of Calendar Days]

  • Debbie Vozniak

    I am trying to do this too. I used the formula =[source data cell] + 8

    I get an error message "# Number Expected"

    If I put the number of days in brackets ([8]) I get an error message "#Unparseable"


    Please help!

  • Travis
    Travis Employee

    Debbie, make sure the formula is located in a Date column.

  • Debbie Vozniak

    Thank you Travis!! It worked. You made my day!!!

  • Travis
    Travis Employee

    Happy to help! Cool

  • Amye
    edited 04/17/16

    Hiya, further to the 'adding' of days, can you refine the formula so that is a cell is a certain value it returns a different date? 


    For example

    If "Priority Level" Cell returns '1' add 1 day, if '2' add 2 days if '3' add 4 days, and the like. 


    I have a feeling it would be a lengthy formula but curious to know if it can be worked.

  • Michael Flores
    edited 10/04/16

    The problem in the above-featured solution is that it put the desired number of days in days in brackets which is very, very frustrating considering how important brackets can be in Smartsheet.


    If you want to add, for example, 90 days to the source date cell in Row 1, try this:


    =[Source Date]1 + 90


    (In the above example, note the '1' after the right bracket means the formula is applied to the source date in Row 1).


    As a newbie myself, I feel these details are important and help stop an unparseable fever from breaking out...

  • ksheppard28396

    I need to go the other way, I need to be able calculate the start date based on the end date minus the duration days.

  • Hi, 


    I'm trying to Calculate a forward date:

    I have used the following formula:

    =[Source Date]1 + 90

    I am trying to calculate the following as per the attached:


    Date +(No of Weeks (Available)*7) which will be give me a date in the future 


  • Colleen Maciejewski

    So - I've gotten a formula to work - calculating a future date based on a source date in a cell in the same row. Then, I tried extending it to calculate based on a criteria in a third cell in same row. For instance, if the duration in [Term]1 = "1 Year", I will add 365 days. If the duration in [Term]1 = "6 Months", I will add 182 days. However, when I try a nested IF statement, I get "unparseable". When I do a simple IF formula, it works but will not recalculate if I change the [Term]1 date.

    Samples:(First one works, but does not change dates when I change the Effective Date, second one is unparseable)

    =IF([Physician's Consent Term]1 = "1 Year", [Physician's Consent Effective Date]1 + 365)

    =IF([Physician's Consent Term]1 = "1 Year", [Physician's Consent Effective Date]1 + 365, IF([Physician’s Consent Term]1 = "6 Months", [Physician’s Consent Effective Date]1 + 182))

  • scott.sandala47436
    edited 05/08/18

    Travis, I'm trying the same thing, but the cell just remains blank.  Figured it out, I was trying to use 'SUM' function

  • Alicia Tang

    I am trying to compute the enddate

    and put `= start + duration1` and the two column get concatenated instead of added.

  • Shane

    I am trying to get a coulmn to trigger a date based off a couple of weeks added from a date I set and keep getting a error message #UNPARSEABLE

    I have read some post on how to calculate days but don't seem to have any luck.

    I tried the below:

    =[Source Data Cell] + [Desired Number of Calendar Days]

    =[Source Data Cell] + 21

  • Ammar T

    Hi All,


    i am new here just want to fix my formula to give me number of days


    =[End Date (Last Day Off)]8 - [Start Date (First Day Off)]8 + " Days"


    above gives 0 but actually its 1 day


    how can i fix it

This discussion has been closed.