# #NUM! error when I am calculating time difference

1. ## #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?  Register To Reply

2. ## Re: #NUM! error when I am calculating time difference  Register To Reply

3. ## 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.  Register To Reply

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

its not letting me upload the excel sheet  Register To Reply

5. ## 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.  Register To Reply

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

oh ok here it is.  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.  Register To Reply

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

This formula might help out
Formula:  `Please Login or Register  to view this content.`  Register To Reply

10. ## 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"  Register To Reply

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

Thank you all for the help it worked!!!!  Register To Reply

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

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