Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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).

like image 505
Lee White Avatar asked Oct 21 '22 11:10

Lee White


1 Answers

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()
like image 176
Jerry Avatar answered Oct 23 '22 03:10

Jerry