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.
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
Microsoft ExcelMicrosoft Office
Last Comment
byundt
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
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.
ask a questionASKER
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/2880
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
John
If both values are time values perhaps you could use TIMEVALUE.
=TIMEVALUE(G267) = TIMEVALUE(K267)