Conditional Formatting from current date

[ExcelRoy]

Hi,

I am looking for a way to notify me of any date that exceeds todays date by 2 days

If at all possible I would like week days only, but not sure if this is too complicated

Many thanks

 

[Scott T]

Maybe where A3 has the date

=AND(A3-TODAY()>2,WEEKDAY(A3,2)<6)

 

[ExcelRoy]

Hi Scott T,

Thanks for replying but it doesn't seem to work?

Thanks

 

[Joe4]

Are you sure that your date is entered as a date and not text?
One way to check is to run the ISNUMBER function against it, and see if it returns TRUE (as all dates in Excel are stored as numbers).
So check to see if this returns TRUE (change A3 to whatever cell the date you want to check it):

=ISNUMBER(A3)

If that returns TRUE, and it is still not working, please provide the date value that is not working so we can test it ourselves.

 

[ExcelRoy]

Hi Joe4,

I run the test and it returned TRUE

I am testing with 01/11/17, 02/11/17, 03/11/17, 04/11/17, 05/11/17

Thanks

 

[ExcelRoy]

Could it be I am using 2007?

 

[Joe4]

I am testing with 01/11/17, 02/11/17, 03/11/17, 04/11/17, 05/11/17

Note that none of those dates are at least 2 days in the future, so none of them would be highlighted!
The first day that would be highlighted would be the 10th of November.

Note that if you want the 9th of November to be highlighted, just make a slight modification to Scott's formula:

=AND(A3-TODAY()>=2,WEEKDAY(A3,2)<6)

 

[Scott T]

I thought you wanted to test if the date was in the future but it looks like you may want to test if the date is 2 days in the past so maybe

=AND(A3-TODAY()<2,WEEKDAY(A3,2)<6)

 

[ExcelRoy]

ah, what a muppet I am, I did not explain myself well enough

I was looking for the formula to highlight say 8th, 9th, 10th etc etc

Its a visual way of monitoring no responses within the current day

Many thanks Joe4

 

[Joe4]

I was looking for the formula to highlight say 8th, 9th, 10th etc etc

Why would it highlight the 8th? That is not two days in the future. If you would like to highlight all future weekdays, just use this variation:

=AND(A3>TODAY(),WEEKDAY(A3,2)<6)