# 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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Have you tried formatting them as numbers, to a few decimal places, to see what's really going on under the hood?

/AJ

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:
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
159
Replies
2
Views
198
Replies
3
Views
113
Replies
11
Views
1K
Replies
5
Views
124

1,214,629
Messages
6,120,630
Members
448,973
Latest member
ChristineC

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