How do I return a date based on a dropdown value?

Hi all,

I am trying to work out a formula to get a cell to return a date based on a dropdown value.

I need the cell to tell me when a matter is due, based on when the matter was received and the priority rating.

the priority levels are (Service Type):

Fast track - 7 days
Basic - 14 days
Standard - 30 days
Complex - 60 days
Complex Advertising - 90 days

I have a column with the date the matter was received, and I need another cell in the same row to show the due date based on the above priority rating..I can get this to work with the following formula, but cant figure out how to get it to work if a different priority level is chosen

=IF([Service Type]1 = "complex advertising - 90 days", [Date Received]1 + 90)

Any help would be greatly appreciated!

Find more posts tagged with

Formulas

Government

Project Management

Comments

Andrée Starå

Hi Tammy,

Try something like this.

To add the other options you'd continue the same pattern with more IF functions.

=IF([Service Type]@row = "Complex Advertising - 90 days"; [Date Received]@row + 90; IF([Service Type]@row = "Fast Track - 7 days"; [Date Received]@row + 7))

The same version but with the below changes for your and others convenience.

=IF([Service Type]@row = "Complex Advertising - 90 days", [Date Received]@row + 90, IF([Service Type]@row = "Fast Track - 7 days", [Date Received]@row + 7))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

You would then use Andree's format to include criteria for the remaining options.

The syntax for an IF statement is this:

=IF(this is true, do this, else)

To nest IF statements, you would basically drop the next one into the previous one's "else" section then close out all of the IF's at the end. Eventually it would look something like this...

=IF(this is true, do this, IF(that is true, do that, IF(something is true, do something)))

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…