Conditional formatting based on various dates

New Contributor

Please can someone help. I've attached 2 columns from my table which I need to apply conditional formatting to

 

if the date in column B has passed and the date in column C is 00/01/1900 mark it as red

If the date in column B is in the next 2 weeks and the date in column C is 00/01/1900 mark it as orange

If the date in column B has passed and the date in column C is anything after 01/01/2021 mark it as green

 

Because the date is pulling from another table, all the entires in the cell read along the lines of "='Assessor Work Tracker.xlsx'!Table1[@[Evidence Received Date]]" rather than the actual date that's in there. 

 

Hopefully what I've asked makes sense, thanks

1 Reply

@Craigm88 

See the attached version.