Calculate difference in dates

[papaexcel]

I need to calculate the amount of time between two dates, and show it in the number of hours and minutes. My dates are formatted to show the time in Zulu time. So 1406 = 2:06PM.

Here is what I have:
A1 = 6/4/08 1406Z
B1 = 6/5/08 0402Z

For this example, the formula should return a result of 13 hours and 58 minutes. With the result, there is no need to desingate Zulu time with a 'Z'. But I need the result in a format that will allow me to calculate an average for all my data.

Thanks!

[martindwilson]

a2-a1 gives 13:56 (note not 13:58)
but to use time in calculations you need to convert it to decimal
=((A2-A1)*1440)/60= 13.93333333

to convert any decimal time after calculation back to hours minutes
=TEXT(E1/24, "[hh]:mm") where E1 is your decimal time

[daddylonglegs]

Try this formula in C1

=REPLACE(LEFT(B1,LEN(B1)-1),LEN(B1)-2,0,":")-REPLACE(LEFT(A1,LEN(A1)-1),LEN(A1)-2,0,":")

Custom format C1 as [h]:mm

[papaexcel]

@martindwilson - Your formula returns #VALUE error. The error occurs when subtracting A1 from A2.

@daddylonglegs - your formula seems to ignore the dates. It calculates the time correctly, but doesn't factor in the fact the A1's date may be many days prior to B1's date.

[windknife]

Try this,

C2

Please Login or Register  to view this content.

Yo can also see attached.
Hope this helps,
windknife

[papaexcel]

@windknife - looks like your formula works great. Thanks.

My only problem now is how to average that number. I have over 2000 rows of data just like the example and I need to create an average of all the differences that I calculate.

[ChemistB]

First format the column with Windknife's formula to custom [h]:mm. Then to his formula, add +0 to the end of it. That forces the string back into a numeric value. Then you can average values. Does that work for you?

[martindwilson]

sorry mate its you data thats not right then, it cant be real times but must be text

[daddylonglegs]

@daddylonglegs - your formula seems to ignore the dates. It calculates the time correctly, but doesn't factor in the fact the A1's date may be many days prior to B1's date.

Should work fine I think, you probably didn't format as [h]:mm as advised.

If you use my formula, as suggested above then you can use a simple AVERAGE function to average the time differences, see attached

[papaexcel]

@daddylonglegs - You are exactly correct sir. I failed to properly format. I kept formatting as h:mm instead of [h]:mm.

Thanks a ton!