Calculating & adding elapsed time

Posted by Kate on February 01, 2002 8:30 AM

I've read allthe posts on this, but can someone further clarify about calculating elapsed time and summarizing the info?

I have a series of beginning and ending times (i.e. 10:02 & 14:17), and I need to have a third column with the amount of time elapsed between the two (i.e. 4:15), expressed in hours and minutes.

So I'll have 4 columns: Date, BeginningTime, EndTime, and ElapsedTime

All of the posts and your tips seem to recommend the following formula:

ElapsedTime=(EndTime+(EndTime &LT; BeginningTime)-BeginningTime)

QUESTIONS:
1) Why couldn't I just do Elapsed=End-Beginning? What's the difference?

2) What does the part in parentheses do? I'm referring to this segment:
(End &LT; Beginning)

3) In my initial attempts, I had the ElapsedTime column formatted as hh:mm, but when I tried to add these numbers up (TotalElapsedTime) it didn't work. Or rather, it was fine up to a total of 24 hours, then the clock seemed to start over. Is the format [h]:mm just an indication to give total elapsed time in hours and minutes?

4) I will sometimes, though rarely, have times that begin on one day and end after midnight (though no actual period calculated is more than 12 hours); will this still work?

5) Is all this really accurate? I need the TOTAL ELAPSED TIME to be accurate.

Thanks for any help you can give. I sent this to Mr. Excel, but then realized maybe someone else might be able to answer as well.

Kate

Posted by IML on February 01, 2002 9:04 AM

You can as long as you don't pass midnight and are only using time (and not time and date) 2) What does the part in parentheses do? I'm referring to this segment:

Excel stores time as fraction of the day. 1 = 24 hours. This is logic put in that say's if the ending time is earlier, it must be the next day. The greater than is boolean the logic that equates to 1 if true, or zero if false.
So with your example it is doing the following

14:17 + 0 - 10:02 = 4:15 without the time formats it would be
.595 +0 - .418 = .177

Alternatively,
using 14:00 and 12:00 you get
12:00 + 24:00 -14:00 = 22:00 or
.5 +1 -.5833 =.91677

This only works if you will only pass midnight once. Otherwise you must enter dates and time and simply use subtraction

Yes.
Yes, but this contradicts your #3 question. 5) Is all this really accurate? I need the TOTAL ELAPSED TIME to be accurate.
Yes Thanks for any help you can give. I sent this to Mr. Excel, but then realized maybe someone else might be able to answer as well. Kate