Emulating or replacing the EXACT function to return TRUE to a close match
Is it possible to emulate the EXACT function to return TRUE to a close match? I have a spreadsheet with two ranges and I need to find matches between two cells, one in Column G and one in Column K. I can't use "=EXACT(G267,K267)" because they contain different ways of displaying the same time.
G267 displays "20:14" and the formula says "8:14:00 PM"
K267 displays "08:14 PM" and the formula says "08:14 PM"
Is there any way to get Excel to return TRUE in this situation? I know I can use VBA to apply the same format for both cells or I can create helper cells to do the same thing but it would be nice to be able to do it without resorting to either of those workarounds.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
If both values are time values perhaps you could use TIMEVALUE.
=TIMEVALUE(G267) = TIMEVALUE(K267)
John Carney
ASKER
Thanks, Syed, it works perfectly! Although I don't understand why since the displayed values are still different.
Norie, the TIMEVALUE function works great for the values in Column K, but returns a "VALUE! error for the values in Column G, but thank you for introducing me to that function
I'm now going to ask a question about close but not exact times. Please be on the lookout.
John
byundt
John,
The EXACT function is used when you need to distinguish "Cat" from "cat". Normally, Excel treats the one as equal to the other. EXACT recognizes that they are different, and will return FALSE to =EXACT("Cat","cat")
From your description, it appears that G267 contains a time/date serial number that has been formatted as hh:mm. I cannot tell whether K267 contains text that looks like a time or an actual date/time serial number.
Even if one or both are text, you can test whether they are within 30 seconds of one another by a TRUE result returned from:
John
If both values are time values perhaps you could use TIMEVALUE.
=TIMEVALUE(G267) = TIMEVALUE(K267)