Conditional formatting to show if date has passed

DOLOMITE73 · #1 04-10-2024, 01:51 PM

In a list of birthdates I'd like to apply conditional formatting so when I open the sheet cells showing birthdays for that year that have passed on that date show green and ones that haven't show red and if they happen to be on that date, say Amber

Example

Open sheet on 10-Apr-2024

Cell value = Colour

01-Jan-2008 = Green
20-Feb-2010 = Green
10-Apr-2010 = Amber
04-May-2011 = Red
24-Sep-2012 = Red

then I open the same sheet on say 11-May-2024

01-Jan-2008 = Green
20-Feb-2010 = Green
10-Apr-2010 = Green
04-May-2011 = Green
24-Sep-2012 = Red

So the formula needs to check the dd-mm have passed but ignore the Year.

Any ideas?

p45cal · #2 04-11-2024, 02:14 AM

use formulae in conditional formatting such as for amber:

Code:

=TODAY()=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

for green:

Code:

=TODAY()>DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

for red:

Code:

=TODAY()<DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

See attached

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Date Conditional Formatting	markdtp	Excel	1	11-17-2022 08:57 AM
Date Conditional Formatting	stu_c	Excel	4	05-22-2022 12:39 AM
Conditional Formatting based on date	teza2k06	Excel	2	11-27-2018 09:55 AM
Date Conditional Formatting	teza2k06	Excel	1	08-09-2014 09:58 AM
Passed due task end date	tcalderini	Project	1	09-19-2013 04:56 AM