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.
Thanks,
John
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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:
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.
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/2880Where does the 2880 come from? There are 24 hours * 60 minutes = 1440 minutes per day. There are double that number (2880) 30 second intervals.
John
If both values are time values perhaps you could use TIMEVALUE.
=TIMEVALUE(G267) = TIMEVALUE(K267)