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

  • KPH
    KPH ✭✭✭✭✭✭
    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

  • KPH
    KPH ✭✭✭✭✭✭
    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:


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

  • Ahh i was missing the extra brackets.


    Thank you very much, you are a time-saver

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!