+ 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
    56

    #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
    Office 365 ProPlus
    Posts
    5,377

    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
    56

    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
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,521

    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
    56

    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
    56

    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
    Office 365 ProPlus
    Posts
    5,377

    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
    56

    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)

Similar Threads

  1. Calculating time difference
    By dwalters in forum Excel General
    Replies: 1
    Last Post: 01-11-2017, 09:39 AM
  2. [SOLVED] Help calculating difference in time
    By jes269 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2014, 10:55 AM
  3. [SOLVED] Calculating time difference?
    By devpp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2013, 02:31 AM
  4. Calculating time difference between PM and AM
    By Paul64 in forum Excel General
    Replies: 4
    Last Post: 10-04-2011, 02:33 PM
  5. Calculating time difference
    By Sibrulotte in forum Excel General
    Replies: 0
    Last Post: 07-28-2009, 09:14 AM
  6. Calculating time difference between two days capturing the overnight time.
    By Monica_La in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2007, 11:22 AM
  7. [SOLVED] error calculating negativ difference time values
    By Xavier in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2006, 07:00 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