Due date formula based off priority

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Auto Fill Date When Column Checked
I want my date column to auto fill when I check the column Verification and Closure. What formula do I use? I want to ensure that the date doesn't change as time goes on.
Employee Reviews - Pulling & Averaging Category Scores
Hi all, thanks in advance for your assistance! I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at: I have created an employee review form that individual managers will complete Each employee will have 3-5 reviewers The form submissions will…
Need Formula for Subtracting Order Quantities from Inventory Quantities
Hi! What formula would I use in Smartsheet to automatically subtract a quantity from one sheet based on what is submitted in another sheet? I have one sheet that is where we will manage the "orders submitted" and on that sheet is different columns for different items that can be ordered and the quantities requested for…