#1
|
|||
|
|||
Date Conditional Formatting
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 :/ |
#2
|
|||
|
|||
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), |
#3
|
|||
|
|||
Quote:
=DATE(YEAR(B4) + B$2, MONTH(B4), DAY(B4))<TODAY()-182 |
#4
|
|||
|
|||
Quote:
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) |
#5
|
||||
|
||||
FWIW, this part
TODAY()-182 may not return the correct date Better use EDATE(TODAY(),-6)
__________________
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting a row based on date | cwkotch | Excel | 1 | 08-28-2021 10:14 PM |
Conditional Formatting based on date | teza2k06 | Excel | 2 | 11-27-2018 09:55 AM |
Conditional Formatting based on dynamic Date | Modifier1000 | Excel | 2 | 09-12-2014 08:53 AM |
Date Conditional Formatting | teza2k06 | Excel | 1 | 08-09-2014 09:58 AM |
Conditional formatting question based on cell date | Cosmo | Excel | 2 | 04-08-2013 12:12 PM |