CODEWITHSUNDEEP
exceldatetimeexcel-formula
Fanny Tirta Sari
Fanny Tirta Sari

Reputation: 21

Excel formula to compare datetime stamp with today's date

I am trying to compare some datetime stamp with yesterday date, so that we can determine whether the datetime stamp is overdue or within 24h.

Excel formula enter image description here

And here is the formula that i am using:

=(IF((A2)<(TODAY()-1),"Overdue","Within 24h"))

Apparently, it is always taking "Within 24h" (FALSE) no matter we put any dates. (TEST column)

I tried to truncate the datetime stamp to dd/MM/yyyy but it is still not working. (TEST2 column).

I tried to use below formula, none of them is working.

  1. =(IF(INT(A2)<(TODAY()-1),"Overdue","Within 24h")) ==> Result "#VALUE!"

  2. =(IF(DATEVALUE(A2)<(TODAY()-1),"Overdue","Within 24h")) ==> Result "#VALUE!"

Upvotes: 0

Views: 3574

Answers (2)

Ambie
Ambie

Reputation: 4977

I have to say those left-aligned dates in your cells look mighty suspicious. Are you certain they're not formatted as text?

If they are text, then you'll need to convert them to a date. You're close with your last formula, but those seconds/milliseconds (if that's what they are) are causing the DATEVALUE() function to fail.

The easiest thing would probably be to strip out those seconds/milliseconds and do your date compare against the resulting string. Assuming the date as text is in cell A1, the formula would be:

=IF(DATEVALUE(LEFT(A1,SEARCH(":?? ",A1)+2)) < TODAY()-1,"Over","OK")

Upvotes: 0

Fanny Tirta Sari
Fanny Tirta Sari

Reputation: 21

Looks like I found the culprit. Apparently, it was due to "Windows Setting => date, time & regional formatting".

Initially, my computer date format was using "mm/dd/yyyy" i.e. "10/27/2021". And none of these formula were working:

  1. =IF(DATEVALUE(LEFT(A2,SEARCH(":?? ",A2)+2)) < TODAY()-1,"Over","OK")
  2. =(IF(INT(B2)<(TODAY()-1),"Overdue","Within 24h"))
  3. =(IF(DATEVALUE(B2)<(TODAY()-1),"Overdue","Within 24h"))

After I changed the date format into "dd/mm/yyyy" and restart my computer. Suddenly all of those formula are working since (in my opinion) the excel is able to recognize column A as a text.

Upvotes: 2

Related Questions