Excel 2007 : DateTime Difference with Military Date/time

**SalemPoe** · 10-28-2010, 01:53 PM

Ok, so I admit I'm feeling a little stupid here

. I have 2 columns that hold date/time in this format: 07/25/2010 14:07:19. I need to find the date/ time difference between the two columns, so column P with 07/25/2010 14:07:19 and column V with 7/25/2010 14:50:13 difference should go into column X with 7/25/2010 00:43:06.

I have tried various formulas and formatting, but nothing has worked yet. HELP, Gurus!

Thank you in advance!!

Salem Poe

**shg** · 10-28-2010, 01:55 PM

Just subtract the smaller from the larger. The result is 00:43:06, not 7/25/2010 00:43:06.

**scottylad2** · 10-28-2010, 02:04 PM

12/01/2010 07:45
13/01/2010 08:45

using TEXT(D44-D43:D43,"d"" days ""h"" hours ""m"" mins """)

yeilds

1 days 1 hours 0 mins

**DonkeyOte** · 10-28-2010, 02:32 PM

@scottylad2 - not that it's relevant to the question but using "d" in a custom number format is risky given the limitation of 31 cumulative days.

**scottylad2** · 10-28-2010, 03:07 PM

Originally Posted by DonkeyOte

@scottylad2 - not that it's relevant to the question but using "d" in a custom number format is risky given the limitation of 31 cumulative days.

true enough, how would one get by that?

**DonkeyOte** · 10-28-2010, 03:24 PM

Possibles:

a) splitting the calc in two - first half returning the INT of the difference with "days" suffix and the second half as before though without the "d" element)

=INT(A2-A1)&" day(s) "&TEXT(A2-A1,"h ""hours ""m"" mins """)

b) fudging the calc such that days become hours, hours minutes and so on and so forth - hardly worthwhile and obviously very risky in terms of latter calcs:

=SUM(INT(A2-A1)/24,MOD(A2-A1,1)/60)

with option b) you could still store as a number and use standard Number Format of: [h]" day(s) "mm" hours "ss" minutes "

but even then if you want to display seconds you have an issue and of course all units are in effect out of kilter