# Calculated date difference in hours not equals inserted hours of same value

1. ## Calculated date difference in hours not equals inserted hours of same value

Hello All,

I am calculating time in "hours:minutes" from difference of dates e.g. CELL A1= 05.08.2017 10:00 CELL B1=05.08.2017 15:30, CELL C1=B1-A1.

Format A1 - Date, Format B1 - Date, Format C1 - Time [h]:mm .

As result C1= 5:30 .

I am writing in to the CELL D1 5:30, same format and value as C1.

Now in CELL E1 I am using function IF(D1=C1; "ok"; "fault") and I have as result "fault", means C1 not equal D1. In Formula Builder values are the same, but function does not work correctly.

What is the reason?

Thank You in advance for any suggestions.

Best Regards,
maro12

2. ## Re: Calculated date difference in hours not equals inserted hours of same value

Originally Posted by maro12
I am calculating time in "hours:minutes" from difference of dates e.g. CELL A1= 05.08.2017 10:00 CELL B1=05.08.2017 15:30, CELL C1=B1-A1.
Format A1 - Date, Format B1 - Date, Format C1 - Time [h]:mm .
As result C1= 5:30 .
I am writing in to the CELL D1 5:30, same format and value as C1.
Now in CELL E1 I am using function IF(D1=C1; "ok"; "fault") and I have as result "fault", means C1 not equal D1
Change the formula in C1 to =--TEXT(B1-A1,"[h]:mm") .

The double negate ("--") converts text to numeric Excel time.

Caveat: I guess you would write =--TEXT(B1-A1;"[h]:mm") -- a semicolon between parameters instead of a comma. I hope you are used to dealing with such regional differences.

In general, when we expect a calculation to be accurate to some degree of precision, we should explicitly round to that degree of precision. For regular numbers, we would use the ROUND function. For time, we can use the TEXT function above.

The difference between C1 and D1 is a common side-effect of 64-bit binary floating-point, which is how Excel stores numeric values. Most decimal fractions cannot be represented exactly in binary floating-point. And the approximation of the same decimal fraction might differ depending on the magnitude of the integer part.

As you might know, Excel time is stored as a fraction of a day. The integer part is a day number, namely the number of days since 31 Dec 1899.

If you format C1 and D1 as Number with 15 decimal places, you will see why they fail to compare equal: C1 is 0.229166666671517, and D1 is 0.229166666666667. Both are approximations of the time 5:30:00.000. They differ by 4.19E-07 seconds.

The difference arises because the approximation of 10:00 and 15:30 with the date 5 Aug 2017 and, therefore, their difference (5:30) is less-accurately represented in binary than the binary representation of the constants 10:00, 15:30 and 5:30 (with a date of zero).

3. ## Re: Calculated date difference in hours not equals inserted hours of same value

Thank You joeu2004, that worked fine!

Apologise for double post, posted by mistake and then could not find way to delete.

Best Regards,
maro12

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1