+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    08-06-2017
    Location
    France
    MS-Off Ver
    Office for Mac 2011
    Posts
    3

    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. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

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

    Quote Originally Posted by maro12 View Post
    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).
    Last edited by joeu2004; 08-07-2017 at 02:13 AM. Reason: minor; caveat

  3. #3
    Registered User
    Join Date
    08-06-2017
    Location
    France
    MS-Off Ver
    Office for Mac 2011
    Posts
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Date Difference in Hours
    By smugglersblues in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-25-2015, 09:35 AM
  2. [SOLVED] Finding date when the sum in a range equals the hours planned
    By TryToUnderstandExcel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2014, 10:02 AM
  3. [SOLVED] Formula Calculated total work hours - now need to remove breaks and standard hours?
    By smeem in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2014, 12:15 AM
  4. Scheduling Data Needed and Bid Hours Calculated for a certain date
    By smirk100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2013, 06:52 PM
  5. Counting difference between hours if outside of business hours
    By joytech22 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-15-2013, 05:03 AM
  6. Hours difference between 2 Date & Time stamps
    By bondi26 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-21-2007, 10:24 PM

Bookmarks

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