CODEWITHSUNDEEP
exceldatedatetimeexcel-formula
Lee White
Lee White

Reputation: 3709

Excel: Compare DateTime to Date

I am using conditional formatting with formulas to highlight dates that occur today (or in the past) in some colour. To check whether a date occurs today, I simply use A1=TODAY(), which works fine. However, some cells contain datetimes, such as 25-Mar-2014 11:14 PM. When I use the above formula on that cell, it returns FALSE, even though today is the 25th of March.

What is the correct way to determine whether a datetime occurs today? I have tried using DATE(A1) to convert the cell to a date without time, but that does not work. I do not want to use Visual Basic for this, nor do something like AND(A1<tomorrow;A1>yesterday).

Upvotes: 6

Views: 8829

Answers (1)

Jerry
Jerry

Reputation: 71538

The thing is that DATE is not used like you mentioned. It is used like so:

=DATE(year, month, date)

You can perhaps use INT which rounds down to the nearest integer (so 25-Mar-2014 11:14 PM becomes 25-Mar-2014 00:00:00 just like TODAY()):

=INT(A1)=TODAY()

Upvotes: 4

Related Questions