Formulas

Resolved · Medium Priority · Version 2019

Thu 20 Oct 2022, 14:13 Delegate Kay said...

Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

Formulas

Hi Team,

Would appreciate if you can kindly point me in the right direction for some formulas I am trying to create.

I am currently creating a excel file where I am prioritising based on Due Dates.

For example Due date column (B) and a Priority column (C)

I would like to create the following formula and also add in conditioning format

If due date is less than 6 months then return 3-Low
If the due date is greater than or equal to 6 months then return 2- Medium
If due date is greater than 1 year then return 1- High

Any help would be greatly appreciated

Thanks

Thu 20 Oct 2022, 14:41 Trainer Marius said...

RE: Formulas

Hello Kay,

Thanks for your interesting question. What I would do, is to write the following nested IF statement to show the status:

=IF(B2>=EDATE(TODAY(),12),1,IF(B2>=EDATE(TODAY(),6),2,3))

This formula assumes that column B holds the dates. The EDATE function looks at dates exactly x months away from a given date (in this case TODAY().

This formula will always assign 1 for >= 1 year, 2 for >= 6 months up to 1 year, and 3 for less than 6 months.

Afterwards, you could apply conditional formatting based on 1, 2 or 3.

I hope this helps.

Kind regards
Marius Barnard
STL

Fri 21 Oct 2022, 14:44 Delegate Kay said...

RE: Formulas

Hi Marius,

Many thanks for your prompt reply.

I will give the above nested formula a try and see if it works for what I need

Much appreciated and thanks again.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Calculate age or service

The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip:

In a blank worksheet, type the birth date in cell A1, using slashes to separate day, month, and year.
In cell A2, type =DATEDIF(A1,TODAY(),"y") and press ENTER.

View all Excel hints and tips