I’m making a spreadsheet that tracks training at my workplace, I want to input a date it was completed on and if it is over 6 months since they completed said training I would like it to highlight “bad” (red) I cannot figure out a formula or condition to achieve this. Any help appreciated.
Hi @Aaronjd,
Welcome!
Let’s say that the date is in cell H39. Click on the menu Format > Conditional > Condition and then select “Cell value” - “Is less than” and the formula NOW()-180. Then select “Bad” as the format to apply.
The function NOW() returns today’s date and 6 months represent about 180 days.
If you want exactly 6 months, this formula is more precise:
DATE(YEAR(NOW()),MONTH(NOW())-6,DAY(NOW()))
(Note the “-6” which subtracts 6 months from the current month and, in case it becomes a negative number, will adapt the year accordingly. DATE() is a very smart function!)
Nice! Learning everyday new ways to do thing!