We help IT Professionals succeed at work.

Emulating or replacing the EXACT function to return TRUE to a close match

John Carney
John Carney asked
on
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.

Thanks,
John
Comment
Watch Question

Have you tried


=value(G267)=value(K267)

NorieAnalyst Assistant

Commented:

John


If both values are time values perhaps you could use TIMEVALUE.


=TIMEVALUE(G267) = TIMEVALUE(K267)

John CarneyReliability Business Tools Analyst II

Author

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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:
=ABS(G267-K267)<1/2880

Open in new window


Where does the 2880 come from? There are 24 hours * 60 minutes = 1440 minutes per day. There are double that number (2880) 30 second intervals.