# Comparing Time Elapsed to Benchmark returning incorrect answer

#### mhenneman

##### New Member
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!

### Excel Facts

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

##### Well-known Member
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
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
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!

Replies
2
Views
49
Replies
4
Views
76
Replies
9
Views
110
Replies
10
Views
174
Replies
1
Views
122