Link to home
Start Free TrialLog in
Avatar of John Carney
John CarneyFlag for United States of America

asked on

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.

Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie


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


Avatar of John Carney


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.

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:

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.