Date based on another date

Clive Walkden

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.

Svendahle

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.

Jeff K.

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

Clive Walkden

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

Clive Walkden

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

Jeff K.

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

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

 

Clive Walkden

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

Jeff K.

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

Jeff K.

Your welcome

 

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

 

 

Clive Walkden

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!