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 based on another date

Clive Walkden
edited 12/09/19 in Archived 2015 Posts

Hi All, I am new to this and was wondering if anyonecan  help me with a date formula?

 

I have a column 'Urgency' that uses dropdown choice - Critical, Urgent, High etc. Each choice has a set amount of time to be completed... Critical = 1 day, Urgent = 2 days etc.

 

I have a 'Due Date' column that I would like to be populated automatically based on the 'Urgency' choice calculated from the System auto 'Created Date' column.

 

ie. System Created Date = 04/08/15

Urgency Choice  = Urgent (2days)

Due Date( autofilled)  = 06/08/15

 

Hope this makes sense and thanks for your help in anticipation.

Tags:

Comments

  • Svendahle
    Svendahle
    edited 08/04/15

    The best way I know of doing this would be to utilize an If/Then statement.

     

    Hints for this can be found here:  http://help.smartsheet.com/customer/portal/articles/775363-using-formulas

     

    Basically, you are keying one field off the value of another and taking action against the field your working with.

     

    In your example above, you have a start date, an "urgency" indicator and a due date. To make the due date add 2 days, use this formula:

     

    =IF([URGENCY]64 = "Urgent", [START DATE]64 + 2, IF([URGENCY]64 = "Critical", [START DATE]64 + 1, [START DATE]64+10))

     

    In this, URGENCY is your indicator, we are in the 64th row, START DATE is the request date.  To that, SmartSheet is adding the requested number of days.


    URGENT = 2

    CRITICAL = 1

    NULL = 10 (meaning, not urgent, you have 10 days to complete the task).

     

    Hope this helps.

  • Good approach, but a more correct answer for this scenario is to use the DATEONLY([ColName]XX) formulat with IF statements:

     

    Assuming 3 columns: Urgency, System Created Date (MM/DD/YY HH:MM), Due Date.

     

    Due Date column (Set column type as Date) will be:

     

    =IF([Urgency]1="Critical", DATEONLY( [SystemCreateDate]1 ) + 1, IF([Urgency]1="Urgent",DATEONLY( [SystemCreateDate]1 ) + 2, DATEONLY( [SystemCreateDate]1 ) +3)))

     

    Check if the Paranthesis are correct. I may have an extra or missed one.

     

    Odd bug.

    DateOnly formula works only if it's =DATEONLY( [ColName]1 ) + Number, but not =Number + DATEONLY( [ColName]1 )

    Critical
  • Guys thanks very much for this, I will give your suggestions a go and report back, thank you.

  • Svendahle, if you don't mind me asking, you write in your explanation... 'we are in the 64th row' ... what does that mean?

  • To answer the 64th row question...It means your formula is reference row 64. Smartsheet's cell reference is [ColumnName]RowNumber format...so it would be [Urgency]64, or in human-speak: Column named Urgency, on the 64th row

     

     

  • Clive Walkden
    edited 08/04/15

    Jeff, not sure if I am doing something wrong but no matter which way I alter your formula I only get the date increased by 3 days, is it somethng to do with the end part of the formula, if I alter the 3 to a 2 for instance the date increasses by 2 but does not do anything if the Urgency is set to any choice. The formula entered is:

     

    =IF(Urgency1 = "Critical", DATEONLY(Created1) + 1, IF(Urgency1 = "Urgent", DATEONLY(Created1) + 2, IF(Urgency1 = "High", DATEONLY(Created1) + 7, DATEONLY(Created1) + 3))

     

  • Guys the penny has dropped....! Jeffs answer on the 64th row did it. I changed the row numbers to match my sheet (row 2 in this case) and it all worked using Jeffs formula.

     Thank you both very much for the help.

     

    As I am only 1 day into Smartsheets I have no doubt that I will be in the community forum alot, again thank you

  • Yes. You should include an IF(Urgency1="XXXX",DATEONLY(Created1)+YYY for every option you have in the dropdown excep the last one. The last option will follow the "false" part of the most inner IF statement:

     

    IF(Urgency1="FirstOption",DATEONLY(Created1) + 1,IF(.... , IF(Urgency1="SecondToLastSelection",DATEONLY(Created1) + XX, DATEONLY(Created1) + NumberDaysForLastOption)))))))

     

    The last bit of Parentheses depend on how many IFs you have

  • Your welcome

     

    Try playing with formulas in Excel. most of the excel and Smartsheet Formulas follow the same basic rules

     

     

  • Clive Walkden
    edited 08/04/15

    Can't tell you how please I am to be able to send off lots of alerts based on that now automated solution, makes my job 1 task easier, my employees more vigilant and my customers happier that there issues are being addressed in due time!

This discussion has been closed.