Conditional Formatting from current date

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,591
Office Version
365, 2016
Platform
Windows
Maybe where A3 has the date
Code:
=AND(A3-TODAY()>2,WEEKDAY(A3,2)<6)
 

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
Hi Scott T,

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

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,479
Office Version
365
Platform
Windows
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):
Code:
=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

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,479
Office Version
365
Platform
Windows
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:
Code:
=AND(A3-TODAY()>=2,WEEKDAY(A3,2)<6)
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,591
Office Version
365, 2016
Platform
Windows
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
Code:
=AND(A3-TODAY()<2,WEEKDAY(A3,2)<6)
 
Last edited:

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,479
Office Version
365
Platform
Windows
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:
Code:
=AND(A3>TODAY(),WEEKDAY(A3,2)<6)
 

Forum statistics

Threads
1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top