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 m
lost.
A couple of places where the dates are teh same, it is giving me a dat
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 mi
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

 

[Guest]

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

 

[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" <[email protected]>
wrote in message