# #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?

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.

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

its not letting me upload the excel sheet

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.

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

oh ok here it is.

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.

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.

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

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

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"

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

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

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

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

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