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)

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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