Conditional Formatting Dates

Good afternoon.

Please bear with me, I'm not the best with Excel.

I have a problem where I need to be able to look at my spreadsheet and have the colours in the backgrounds quickly show me when a job is... in date, coming due or overdue. I need colours to change each time I open the spreadsheet so if I open it a week later they will change depending on the change in date.

I have tried to use conditional formatting to show the dates in different colours. The problem I have is that sometimes the rules work and sometimes not, other times the cell is blank like there is no rules.

These are the rules I am using at the moment are;

Coming due - =AND($B2-TODAY()>=120,$B2-TODAY()<1)
Due - =B2-TODAY()>120
In date - =TODAY()-B2>0
Not required - Blanks rule

I have attached a sheet to show in action what I have.

Any help would be appreciated.

Originally Posted by AliGW

Welcome to the forum.

It's not your CF rules. The two cells that don't work are not dates - they are text. You can check this by changing all date cells' formatting to general - real dates will show as a 5-digit serial number.

Excel 2016 (Windows) 32 bit

	G	H	I	J
8	31-06-2021
9				44177
10				44336
11				20-20-2021

Sheet: Jobs

Thank you Ali. That turned out to be part of my problem indeed :D

Originally Posted by FDibbins

1st problem....not all your dates are real dates. G8 and J11 are actually text looking like dates - you need to change those
2nd problem...you need to remove the absolute from the 1st formula =AND($B2-TODAY()>=120,$B2-TODAY()<1)
3rd problem...The logic in that same formula looks off. You are testing for >= today+120 AND 1 day less than today. Take another look at that 1, I think it should be something like...
=AND(B2-TODAY()<=120,$B2-TODAY()>1)

Hi FDibbins,

Thank you for the help, the problems are back under control again...

I think the way I was thinking was one of the hinderances in this. I was thinking I wanted the number (B2-TODAY) to be greater than 120 and the other to be less than 1 so that would leave the numbers in between.

I still had a problem with the coming due date not working elsewhere in the area then I removed both absolutes (they were there from me trying to fix the issue earlier) now everything seems to be running fine.