Overdue date

[SaraWitch]

Hello peeps,

I have a start date in A2, a due date in B2 and a completion date in C2. The due date automatically updates when a completion date is entered in C2 (as this is a recurring annual review).

I would like the row to turn red if there is a due date that has passed and C2 is either blank and today's date is greater than due date or there is a date last completed (date in C2) and today's date is greater than (new) due date.

I started trying to do a formula for conditional formatting on just the dates in A2 to C2, but couldn't work it out. I then thought that by adding a status column I could more easily apply conditional formatting but now I'm tying myself up in knots to get the correct status returning (example below)!

I hope this makes sense and I would be really grateful if anyone can help me out... Ta very muchly!

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G
1	Start date	Review due date	Date completed	Status	Status should be
2	01/06/2022	01/06/2025	01/06/2024	FALSE	Pending		Would like row to turn red if there is a due date that has passed and C2 is either blank and today's date is greater than due date or there is a date last completed (date in C2) and today's date is greater than (new) due date
3	01/06/2023	31/05/2024		FALSE	Overdue
4	01/06/2022	01/06/2024	02/06/2023	FALSE	Overdue
Due Date

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2="","",IF(C2="",A2+365,C2+365))
D2:D4	D2	=IF($A2="","",AND(C2="",TODAY()<B2,"Pending,AND(c2="",TODAY(0>B2,""Overdue",AND(C2>0,TODAY()>B2,"Overdue")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G4	Expression	=$D2="Overdue"	text	NO

 

[SaraWitch]

Just noticed some rookie mistakes in D2 formula (missing Pending quotation and an incorrect TODAY function (zero instead of bracket))! So I corrected, but still didn't work. I've tried changing slightly, but still not working:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G
1	Start date	Review due date	Date completed	Status	Status should be
2	01/06/2022	01/06/2025	01/06/2024	FALSE	Pending		Would like row to turn red if there is a due date that has passed and C2 is either blank and today's date is greater than due date or there is a date last completed (date in C2) and today's date is greater than (new) due date
3	01/06/2023	31/05/2024		FALSE	Overdue
4	01/06/2022	01/06/2024	02/06/2023	FALSE	Overdue
Due Date

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2="","",IF(C2="",A2+365,C2+365))
D2:D4	D2	=IF($A2="","",AND(C2="",B2>TODAY(),"Pending",AND(C2="",B2<TODAY(),"Overdue",AND(C2>0,B2<TODAY(),"Overdue"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G4	Expression	=$D2="Overdue"	text	NO

 

[etaf]

is this just if the DUE Date in column B is earlier than today()
=$B2<today()
to stop blanks highlighting as that seen as a date 0/1/1900
then
=AND( $B2<>"", $B2<today() )

Book1
	A	B	C	D	E	F
1	Start date	Review due date	Date completed	Status	Status should be
2	6/1/22	6/1/25	6/1/24	FALSE	Pending
3	6/1/23	5/31/24		FALSE	Overdue
4	6/1/22	6/1/24	6/2/23	FALSE	Overdue
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2="","",IF(C2="",A2+365,C2+365))
D2:D4	D2	=IF($A2="","",AND(C2="",TODAY()<B2,"Pending,AND(c2="",TODAY(0>B2,""Overdue",AND(C2>0,TODAY()>B2,"Overdue")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E11	Expression	=AND($B2<>"", $B2<TODAY())	text	NO

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:E10000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND( $B2<>"", $B2<today() )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[SaraWitch]

Oh my goodness, I was overthinking it!!!

Thank you so much, @etaf, this is perfect!

 

[etaf]

you are welcome