Comparing Time Elapsed to Benchmark returning incorrect answer

mhenneman

New Member
Joined
Apr 23, 2013
Messages
15
I have two columns Date/Time of Request and Date/Time of Response. (These are custom formatted as MM/DD/YYYY HH:MM AM/PM). In column C I calculate the timeliness of response by subtracting Response - Request. For example, 11/24/2012 10:00 AM - 11/14/2012 10:00 AM returns 240:00 (formatted as [hh]:mm).

Where I am having issues is comparing it to the benchmark. I have a benchmark set of 48:00 (formated as [hh]:mm. I am using the following formula: If(timeliness <= benchmark, "yes", "no"). However, this formula returns a yes response, when it shouldn't (clearly 240:00 is not less than 48:00).

I have tried various formatting arrangements with no luck. Any insight into why this is happening?

Thanks!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Have you tried formatting them as numbers, to a few decimal places, to see what's really going on under the hood?

/AJ
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,800
Office Version
  1. 2010
Platform
  1. Windows
I have two columns Date/Time of Request and Date/Time of Response. (These are custom formatted as MM/DD/YYYY HH:MM AM/PM). In column C I calculate the timeliness of response by subtracting Response - Request. For example, 11/24/2012 10:00 AM - 11/14/2012 10:00 AM returns 240:00 (formatted as [hh]:mm).

Where I am having issues is comparing it to the benchmark. I have a benchmark set of 48:00 (formated as [hh]:mm. I am using the following formula: If(timeliness <= benchmark, "yes", "no"). However, this formula returns a yes response, when it shouldn't (clearly 240:00 is not less than 48:00).

First, when comparing a calculated time, it is prudent to round the calculation to the minute, for example. So if C1 has the formula =B1-A1, change it to =--TEXT(B1-A1,"[h]:mm").

The double-negate (--) arithmetic converts text to a numeric value. Any equivalent arithmetic accomplishes the same thing; for example, multiply by one or add zero.

The reason is: Excel time is stored as a decimal fraction, and arithmetic with decimal fractions often incur infinitesimal differences due to the way that Excel stores numbers (64-bit binary floating-point).

However, that does not explain why C1<=benchmark returns a false positive, where benchmark is 48:00.

I suspect that "benchmark" is text, not a numeric value. Perhaps your wrote C1<="48:00". Alternatively, if you wrote C1<=X1, perhaps =ISTEXT(X1) returns TRUE.

The quick fix might be to write C1<=--"48:00" or C1<=--X1.

But if "benchmark" is a cell reference like X1, it would be better to fix X1 so that it contains a numeric value, not text.
 
Last edited:

mhenneman

New Member
Joined
Apr 23, 2013
Messages
15
First, when comparing a calculated time, it is prudent to round the calculation to the minute, for example. So if C1 has the formula =B1-A1, change it to =--TEXT(B1-A1,"[h]:mm").

The double-negate (--) arithmetic converts text to a numeric value. Any equivalent arithmetic accomplishes the same thing; for example, multiply by one or add zero.

The reason is: Excel time is stored as a decimal fraction, and arithmetic with decimal fractions often incur infinitesimal differences due to the way that Excel stores numbers (64-bit binary floating-point).

However, that does not explain why C1<=benchmark returns a false positive, where benchmark is 48:00.

I suspect that "benchmark" is text, not a numeric value. Perhaps your wrote C1<="48:00". Alternatively, if you wrote C1<=X1, perhaps =ISTEXT(X1) returns TRUE.

The quick fix might be to write C1<=--"48:00" or C1<=--X1.

But if "benchmark" is a cell reference like X1, it would be better to fix X1 so that it contains a numeric value, not text.

Thank you,this worked!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top