Elapsed time question

[Johnfli]

I have a spreadsheet that has a date in one column, Time in the next, date
in the next column, then time in teh column after that. I need to find out
how much time elapsed.
For example

In cell A1 the date is 1/9/2006
In cell B1 the time is 10:55

In C1 the date is 1/22/2006
in D1 the Time is 14:00

So I need to find the the elapsed time between these 2 times.


Thanks in advance

[daddylonglegs]

Here's a few options....

To give elapsed time in hours

=D1+C1-B1-A1

format as [h]:mm

To give the result in days, hours, minutes (if elapsed period will always be less than 32 days)

format as

d "days" h "hours" m "mins"

To give a result in days, hh:mm (for any time period) try this formula

=INT(D1+C1-B1-A1)&" days "&TEXT(MOD(D1+C1-B1-A1,1),"hh:mm")

[johnfli]

WOrks well for teh most part, but there are a few items that has me lost.
A couple of places where the dates are teh same, it is giving me a date difference of -38771, yet the time difference it calculates correctly.

Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min and 34 sec.

Here are the dates and time for that item:
A1 = 2/16/2006
b1 = 18:45:12
c1 = 2/16/2006
d1 = 19:16:48

As you see, for days, it should say "0"
and time it should say a little over 30 min.

[LHSallwasser]

Hi:
That's odd; I just tried the same formula with your values and do get the
correct answer 0:31:36 Are you sure you've formatted the cell that will
receive the calculation as custom? Should be [h]:mm:ss

A1 B1 C1 D1 E1
2/16/2006 18:45:12 2/16/2006 19:16:48 0:31:36

Formula in E1 is D1+C1-B1-A1

Thank you, daddylonglegs: this was exactly the format code I've been
looking for.

Best regards,
LHSallwasser

"johnfli" wrote:

>
> WOrks well for teh most part, but there are a few items that has me
> lost.
> A couple of places where the dates are teh same, it is giving me a date
> difference of -38771, yet the time difference it calculates correctly.
>
> Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min
> and 34 sec.
>
> Here are the dates and time for that item:
> A1 = 2/16/2006
> b1 = 18:45:12
> c1 = 2/16/2006
> d1 = 19:16:48
>
> As you see, for days, it should say "0"
> and time it should say a little over 30 min.
>
>
> --
> johnfli
> ------------------------------------------------------------------------
> johnfli's Profile: http://www.excelforum.com/member.php...o&userid=32043
> View this thread: http://www.excelforum.com/showthread...hreadid=517554
>
>

[daddylonglegs]

WOrks well for teh most part, but there are a few items that has me lost.
A couple of places where the dates are teh same, it is giving me a date difference of -38771, yet the time difference it calculates correctly.

Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min and 34 sec.

Here are the dates and time for that item:
A1 = 2/16/2006
b1 = 18:45:12
c1 = 2/16/2006
d1 = 19:16:48

As you see, for days, it should say "0"
and time it should say a little over 30 min.

I don't believe that formula will give incorrect results - it may be that your times are not as they seem. What do you get if you temporarily format D1 or B1 as general, you should see a number between 0 and 1, if not this will throw out the formula.

Do you have formulas generating the times or are they just entered manually?

[Johnfli]

The data is entered manualy. I think it must be some funky format in teh
cell becasue I have about 100 rows of different times and dates, and about
95% work just fine.
One the cell where I get teh result of -38753, when I set the cell format to
general, it changes teh date to 38754




"daddylonglegs" <daddylonglegs.240ogy_1141260301.2117@excelforum-nospam.com>
wrote in message
news:daddylonglegs.240ogy_1141260301.2117@excelforum-nospam.com...
>
> johnfli Wrote:
>> WOrks well for teh most part, but there are a few items that has me
>> lost.
>> A couple of places where the dates are teh same, it is giving me a date
>> difference of -38771, yet the time difference it calculates correctly.
>>
>> Another place on the sheet, it lists it as a -1 day and 5 hours, 46 min
>> and 34 sec.
>>
>> Here are the dates and time for that item:
>> A1 = 2/16/2006
>> b1 = 18:45:12
>> c1 = 2/16/2006
>> d1 = 19:16:48
>>
>> As you see, for days, it should say "0"
>> and time it should say a little over 30 min.
>
> I don't believe that formula will give incorrect results - it may be
> that your times are not as they seem. What do you get if you
> temporarily format D1 or B1 as general, you should see a number between
> 0 and 1, if not this will throw out the formula.
>
> Do you have formulas generating the times or are they just entered
> manually?
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=517554
>