Finding the difference between two dates and times

[Harper95th]

Hello Everyone,

I am having difficulty finding the difference between two times and dates in Excel. I have researched this on Google and tried several formulas but none have worked for me. The format the data is currently in can be seen below:

A1: 1/3/2014 11:00:02.230 B1: 2/7/2014 08:42:37.637
A2: 2/12/2014 07:51:58.663 B2: 2/13/2014 05:36:31.893

(Note all times are military time format)

I want to display the difference in C1 in a format along the lines of "dd hh:mm". The data for seconds and fractions of a second is included in the source data but is not needed in the final results.

If the date and time falls on the same day, I have no trouble doing a simple "B3-A3" to determine the answer. For example:

A3: 2/5/2014 09:56:06.273 B3: 2/5/2014 10:39:51.34

Gives me an answer of: 0:43

Trying to do the same for A1 and B1 gives me this: "03 21:42" when I format it to "dd hh:mm".

Is there a formula that would give me the correct answer in both of these cases?

Thanks everyone for your help!

[AlKey]

Maybe this one

=CONCATENATE(TEXT(FLOOR(B1-A1,1),"@")," Days",TEXT(B1-A1," h:mm:ss"))

	A	B	C
1	1/3/2014 11:00:02.230	2/7/2014 8:42:37.637	34 Days 21:42:35
2	2/12/2014 7:51:58.663	2/13/2014 5:36:31.893	0 Days 21:44:33
3	2/5/2014 9:56:06.273	2/5/2014 10:39:51.340	0 Days 0:43:45

[6StringJazzer]

Another approach that gives the same results:

Please Login or Register  to view this content.

[daddylonglegs]

The reason for the problem when using dd:hh:mm format is that "dd" actually shows "day of month" not elapsed days so when you get 3 for your example that's because the 34 days elapsed is converted to a date - 3rd Feb 1900 and dd returns the "day" part of that date, i.e. 3

You can shorten 6StringJazzer's formula a little - MOD function isn't required

=INT(B1-A1)&" " &TEXT(B1-A1,"hh:mm")

[Harper95th]

Huge thanks to all three of you! I really appreciate the help. Each of those formulas worked great.

AlKey, I understand your formula except for the "@" piece. Can you explain what that is doing?

[AlKey]

Huge thanks to all three of you! I really appreciate the help. Each of those formulas worked great.

AlKey, I understand your formula except for the "@" piece. Can you explain what that is doing?

Used with TEXT function to format as text.

[AlKey]

Thank you for your feedback!

[Harper95th]

You're welcome! Thanks again to everyone for the help!

[6StringJazzer]

You're welcome! Thanks for the rep, and thanks for marking your thread Solved!