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!
 

Excel Facts

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

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

Banned user
Joined
Mar 2, 2014
Messages
3,080
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!
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top