Automatically add a due date from a creation date

Jordan.soper

Hello

I am currently using a smartsheet as a job tracker, where team members can fill out a form with their requests.

I have a column labeled urgency that has, Low, Medium, High and on hold, the different urgency dictates the due date.

What I have at the moment is the date is saved when a new row is created, and I want the due date to fill automatically, with differing delays, i.e. Low urgency 5 days, medium, 3 days, high, same day

do you know of a way I can have this automatically apply?

Thanks

KPH

Hi @Jordan.soper

I would use a formula to do this.

I've set up an example like this, you will need to adapt the column headings if yours differ:

In the due date cell (which must be a date format) you can have an IF function:

=IF(Urgency@row = "Low", [Created Date]@row + 5)

This evaluates the Urgency cell, and if it is equal to low it adds 5 days to the Created Date. Note these are DAYS, not working days, which is an option (see below).

We can nest another IF, to be evaluated if the first is not true by adding a comma after the plus 5 and inserting another IF, like this:

=IF(Urgency@row = "Low", [Created Date]@row + 5,IF(Urgency@row = "Medium", [Created Date]@row + 3))

And again for High, like this:

=IF(Urgency@row = "Low", [Created Date]@row + 5,IF(Urgency@row = "Medium", [Created Date]@row + 3,IF(Urgency@row = "High", [Created Date]@row)))

If none of these things are true, no Due Date will be created.

You can learn more about IF here:

https://help.smartsheet.com/function/if

And about working days here:

https://help.smartsheet.com/function/workday

KPH

Hi @Jordan.soper

I would use a formula to do this.

I've set up an example like this, you will need to adapt the column headings if yours differ:

In the due date cell (which must be a date format) you can have an IF function:

=IF(Urgency@row = "Low", [Created Date]@row + 5)

This evaluates the Urgency cell, and if it is equal to low it adds 5 days to the Created Date. Note these are DAYS, not working days, which is an option (see below).

We can nest another IF, to be evaluated if the first is not true by adding a comma after the plus 5 and inserting another IF, like this:

=IF(Urgency@row = "Low", [Created Date]@row + 5,IF(Urgency@row = "Medium", [Created Date]@row + 3))

And again for High, like this:

=IF(Urgency@row = "Low", [Created Date]@row + 5,IF(Urgency@row = "Medium", [Created Date]@row + 3,IF(Urgency@row = "High", [Created Date]@row)))

If none of these things are true, no Due Date will be created.

You can learn more about IF here:

https://help.smartsheet.com/function/if

And about working days here:

https://help.smartsheet.com/function/workday

Jordan.soper

Fantastic, that worked really well thank you.

I can't figure out how to get the WORKDAY function to work with a multiple IF Statment, is it possible for you to provide an example?

Thanks again

KPH

To use WORKDAY in your IF statement, rather than using

[Created Date]@row + 5

Which is Created Date plus 5 days

You can use

WORKDAY([Created Date], 5)

Which takes Created Date and adds 5 working days

So your formula would look like this (changes in bold)

=IF(Urgency@row = "Low", WORKDAY([Created Date]@row, 5), IF(Urgency@row = "Medium", WORKDAY([Created Date]@row, 3), IF(Urgency@row = "High", [Created Date]@row)))

Jordan.soper

Ahh i was missing the extra brackets.

Thank you very much, you are a time-saver