CODEWITHSUNDEEP
excelexcel-formula
MOZ
MOZ

Reputation: 21

How to Match Specific Date with Date Column in Excel?

I am trying to match a specific date with the available dates in Date column (CHECKTIME) but failed to do so.

Formula: =IF(MATCH(TEXT($I$4,"m/d/yyyy"),TEXT(Table_CHECKINOUT4[CHECKTIME],"m/d/yyyy"),0),"Yes")

Result: #N/A

Where I am doing wrong or what can be updated?

Upvotes: 0

Views: 1200

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Match will return an error if the value is not found. If the criteria in an IF returns an error it will error the whole formula:

=IF(ISNUMBER(MATCH($I$4,Table_CHECKINOUT4[CHECKTIME],0)),"Yes","No")

enter image description here

One note this works if both the value being searced and the lookup values are true dates without any time portion to the number stored.

Remember what you see is not what is stored. Dates are numbers, the number of days since 12/31/1899, with the time being the decimal of a full day. 1/1/2020 12:00:00 PM is stored as 43831.5. That number is what the Excel sees no matter how the format mask is. So if it is formatted yyyy-mm-dd it will show 2020-01-01 but the number will still be 43831.5.

If your data has a time portion we can use COUNTIFS instead:

=IF(COUNTIFS(Table_CHECKINOUT4[CHECKTIME],">="&INT($I$4),Table_CHECKINOUT4[CHECKTIME],"<"&INT($I$4)+1),"Yes","No")

Upvotes: 1

Related Questions