# 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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### 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

##### Banned user
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
3
Views
465
Replies
5
Views
274
Replies
5
Views
269
Replies
4
Views
429
Replies
7
Views
305 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,598
Messages
5,832,645
Members
430,150
Latest member
amitk1 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back