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

Options
edited 12/09/19

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:

• edited 08/04/15
Options

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.

• Options

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
• Options

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

• Options

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

• Options

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

• edited 08/04/15
Options

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

• Options

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

• Options

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

• Options

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

• edited 08/04/15
Options

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.