We help IT Professionals succeed at work.

Excel formula that matches time values that are close but not exact

John Carney
John Carney asked
on
I'm trying to find a formula that will match two times that are very close to each other but not exact. They're also formatted different so I can't use the EXACT function. Pleae refernce the previous question which will explain what the situation is: https://www.experts-exchange.com/questions/29171053/Emulating-or-replacing-the-EXACT-function-to-return-TRUE-to-a-close-match.html

In any event unlike the two cells in that question, I have some situations where the times are off by a minute or so.


When the times are identical but formatted different I can use this formula: "=VALUE(G267) = VALUE(K267)" where G267 = 20:14 and K267 = 8:14 PM

But what do I do when G267 = 9:22 and K267 = 09:23 AM?

Thanks in advance!

John
Comment
Watch Question

View Solution Only

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
=ABS(G267-K267)<TIME(0,2,0)      returns TRUE if less than 2 minutes difference.
NorieAnalyst Assistant

Commented:

John


If both values are time values you could try something like this.


=IF(ABS(G267-K267) < TIME(0,2,0), "Match", "No Match")



John CarneyReliability Business Tools Analyst II

Author

Commented:
Wow, you're feeding my need for instant gratification. It works perfectly plus unlike another question I asked recently, I instantly understand it!

Thanks,
John