Due date formula based off priority

agonzales
agonzales ✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to have calculate a due date based off the priority (high , medium, or low), add 1,2, or 4 days to the date submitted. I'm using something like the below, but getting an error. Any help?

=IF([Business Priority]@row = "High"), [Date Submitted]@row + 1, IF([Business Priority]@row = "Medium"), [Date Submitted]@row + 2, IF([Business Priority]@row = "Low"), [Date Submitted]@row + 4)))

 

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi there, you're definitely on the right track. You just have to remove the early closing parenthesis at the end of each IF statement. You close them all at the end like this.

    =IF([Business Priority]@row = "High", [Date Submitted]@row + 1, IF([Business Priority]@row = "Medium", [Date Submitted]@row + 2, IF([Business Priority]@row = "Low", [Date Submitted]@row + 4)))

    Does this one work for you? 

  • Thanks for help, that got me sorted out. I appreciate it!

     

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You're welcome! I'm glad I could assist you. 

  • Pam Dunn
    Pam Dunn ✭✭✭✭✭

    @Mike Wilday how would I add WORKDAY to this formular so my due date only lands on a working day M-F? This is what I did but it comes back blank.


    =IF([Requester Priority]@row = "Next Day", WORKDAY([Date Requested]@row + 1, IF([Requester Priority]@row = "2-4 Days", WORKDAY([Date Requested]@row + 4, IF([Requester Priority]@row = "1 Week", WORKDAY([Date Requested]@row + 5))))))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Pam Dunn

    It looks like you're not closing off each WORKDAY( ) <

    The structure for the WORKDAY function is as follows:

    WORKDAY(Date, Number of days, Optional Holidays)

    So instead of

    WORKDAY([Date Requested]@row + 1,

    try

    WORKDAY([Date Requested]@row, 1)


    =IF([Requester Priority]@row = "Next Day", WORKDAY([Date Requested]@row, 1), IF([Requester Priority]@row = "2-4 Days", WORKDAY([Date Requested]@row, 4), IF([Requester Priority]@row = "1 Week", WORKDAY([Date Requested]@row, 5))))


    Cheers,

    Genevieve

  • Dave French
    Dave French ✭✭✭

    I'm trying to do the same thing, however, reference another sheet for the intervals. Below is my formula I'm using. Can someone help me understand why it returns #UNPARSEABLE?


    =IF(PRIORITY@row = "Low"), INSPECTION@row + {Priority Inspection Interval Range 1}, IF(PRIORITY@row = "High"), INSPECTION@row + {Priority Inspection Interval Range 2}, IF(PRIORITY@row = "Medium"), INSPECTION@row + {Priority Inspection Interval Range 3},))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Dave French

    The unparseable is caused by syntax errors which I corrected below. There may, however, be data errors in your formula, depending on what your cross sheet ranges refer to. If your cross sheet references are all referring to single cells on your other sheet, all is good (eg, {Priority Inspection Interval Range 1} refers to one single cell and {Priority Inspection Interval Range 2} refers to another single cell, etc). IF your cross sheet ranges are referring to entire columns, smartsheet will not know what to add/concatenate to your Inspection@row.

    Below is your formula with the syntax corrected:

    =IF(PRIORITY@row = "Low", INSPECTION@row + {Priority Inspection Interval Range 1}, IF(PRIORITY@row = "High", INSPECTION@row + {Priority Inspection Interval Range 2}, IF(PRIORITY@row = "Medium", INSPECTION@row + {Priority Inspection Interval Range 3})))

    Does this work for you?

    Kelly

  • Dave French
    Dave French ✭✭✭

    @Kelly Moore

    Thank you so much!! It worked. I was racking my brain on what I was doing wrong. I appreciate it Kelly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!