Automatically add a due date from a creation date
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
Best Answer
-
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:
And about working days here:
Answers
-
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:
And about working days here:
-
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
-
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)))
-
Ahh i was missing the extra brackets.
Thank you very much, you are a time-saver
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!