Hi all,


I want to be able to do a spreadsheet for peoples training, some training valid for longer times which is shown in the file attached.

what I want to be able to do is add the amount of years valid for onto the last date date taken and then show either show the following
White - 1 Year left or more
Green - 6 Months Left
Orange - 3 Months Left
Red - 1 Month Or Less

I was thinking of having another column showing the date due (Date Taken + Row 2 in years) then hide that column and do the conditional formatting from that but didn't know if there was a better way?

Problem I have is I don't know the formula to add the row 2 onto the years and also in conditional formatting how you would do for example (B4 + B2 = <Today()

I do not know if I am just over thinking it, been a very long time since doing Excel formulas :/

Attached Files

Training.xlsx (9.9 KB, 8 views)

Here you go!

I set the Conditional Format for overtime, you can easily add formats for various warnings (adding/removing the number of years/months in DATE() function in formula),

Attached Files

Training.xlsx (10.4 KB, 8 views)

thank you!, I am guessing if I wanted a conditional format after 6 months would it be
=DATE(YEAR(B4) + B$2, MONTH(B4), DAY(B4))<TODAY()-182

OR
the 4 conditional format formulas may be
=(DATE(YEAR(B4) + B$2, MONTH(B4)-12, DAY(B4))<TODAY())
=(DATE(YEAR(B4) + B$2, MONTH(B4)-6, DAY(B4))<TODAY())
=(DATE(YEAR(B4) + B$2, MONTH(B4)-3, DAY(B4))<TODAY())
=(DATE(YEAR(B4) + B$2, MONTH(B4)-1, DAY(B4))<TODAY())
(And you have to combine the order of formulas, and StopIfTrue, to get right colors used)

FWIW, this part
TODAY()-182
may not return the correct date

Better use EDATE(TODAY(),-6)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post