Due date formula based off priority

agonzales

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

 

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? 

agonzales

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

 

 

Mike Wilday

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

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.

https://community.smartsheet.com/discussion/comment/327235#Comment_327235

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

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

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

@Kelly Moore

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