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