+ Reply to Thread
Results 1 to 12 of 12

#NUM! error when I am calculating time difference

  1. #1
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    52

    #NUM! error when I am calculating time difference

    I have this formula to calculate the time difference between two dates and two times.
    =(INT(V4-AB4)&" days, "&HOUR(V4-AB4)&" hours, "&MINUTE(V4-AB4)&" minutes and "&SECOND(V4-AB4)&" seconds")
    they are pulling the data from these two formulas.
    =TEXT(S4, "mm/dd/yyyy")&" "&TEXT(U4,"hh:mm:ss")
    =TEXT(Y4, "mm/dd/yyyy")&" "&TEXT(AA4,"hh:mm:ss")
    they are pulling data from two columns that i converted a number to army using this formula.
    =TEXT(T4,"00\:00\:00")+0
    And they are both displaying the date and time.
    The thing is, is that for this example the dates are the same but the times are different and I get this error.
    I used the formula on another line with two different dates and times and it works.

    Any Ideas of why it is not working when the dates are the same?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,906

    Re: #NUM! error when I am calculating time difference

    Can you upload sample workbook demonstrating your issue?
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: #NUM! error when I am calculating time difference

    I agree with CK76. It's difficult to replicate what you are doing without a sample workbook.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  4. #4
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    52

    Re: #NUM! error when I am calculating time difference

    its not letting me upload the excel sheet

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,603

    Re: #NUM! error when I am calculating time difference

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    52

    Re: #NUM! error when I am calculating time difference

    oh ok here it is.
    Attached Files Attached Files

  7. #7
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: #NUM! error when I am calculating time difference

    I believe the error is caused because D3 is smaller than I3 and when it subtracts I3 from D3, it comes out to a negative number. If you switch all the (D3-I3) to (I3-D3) it works.

  8. #8
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    52

    Re: #NUM! error when I am calculating time difference

    I have to have it D-I because its a calculation of the date and time some should be done and when it actually was done. In terms of time and date. I have switched it and you can see the second line now doesn't work.
    Attached Files Attached Files

  9. #9
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: #NUM! error when I am calculating time difference

    This formula might help out
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,906

    Re: #NUM! error when I am calculating time difference

    You don't need to convert time and date into text really...
    You can simply add time to date to get combined date time value.
    I.E. In D3: =A3+C3, In I3: ==F3+H3

    Alternate to gmr4evr1's method, you can also do...

    =INT(ABS(D3-I3))&" days, "&HOUR(ABS(D3-I3))&" hours, "&MINUTE(ABS(D3-I3))&" minutes and "&SECOND(ABS(D3-I3))&" seconds"

    But, personally, I'd use something like below to indicate when D3 < I3.
    =IF(SIGN(D3-I3)<0,"Minus ","")&INT(ABS(D3-I3))&" days, "&HOUR(ABS(D3-I3))&" hours, "&MINUTE(ABS(D3-I3))&" minutes and "&SECOND(ABS(D3-I3))&" seconds"

  11. #11
    Registered User
    Join Date
    07-17-2017
    Location
    New Jersey
    MS-Off Ver
    2013
    Posts
    52

    Re: #NUM! error when I am calculating time difference

    Thank you all for the help it worked!!!!

  12. #12
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: #NUM! error when I am calculating time difference

    You're welcome, glad that a solution was found.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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