Excel - Elapsed Time - Calc.

[Rollingthunder]

I know this subject has been touched on here and I have diligently searched and pulled up three related threads. I've messed around with every combination I can think of and failed so far.

I want to calculate elapsed time when the end time is over 24:00

eg.

Time on: 23:00
Time off: 00:30
Elapsed time = 1:30

thanks.

[Tinstaafl]

I have a spreadsheet I made to calculate flight times to do just that.

I'll send you a copy if you like.

The way I found was to do a test to see if the end time was numerically less than the start time eg start at 22:00, finish at 02:00.

If yes, then add '1' to the finish time & then subtract start from finish.

Excel stores time as a decimal fraction, days are a whole number. Adding '1' has the effect of adding one whole day to the finish time ie 24 hrs.

[Rollingthunder]

Thanks very much Tinstaafl. Please check your PM.

I worked it out formatted as numbers, but there's probably a future need to maintain it as time formatting for use with other data.

Cheers.

[dolly737]

Hi Rollingthunder:

You can get around this quite easily by entering 24:30 (or the respective value +24, e.g. 25:45 instead of 01:45).

happy landings

[Rollingthunder]

Hi Dolly and thanks. Unfortunately it's a question of taking crew data and dropping it into another spreadsheet for analysis. Getting a couple of hundred people to change the way they enter time would be harder than sorting out the formatting problem.

[Agent86]

If you are familiar with visual basic or modifying macros try this

Add this code to a module in the workbook

Function Timediff(startT, EndT)
Dim timediff1
timediff1 = DateDiff("n", startT, EndT)
If startT > EndT Then timediff1 = timediff1 + 1440
Timediff = TimeSerial(Int(timediff1 / 60), timediff1 Mod 60, 0)
End Function

then in the sheet where you want the calc to take place use the function Timediff

ie if start time is in cell D3 and end time is is cell D4 and you want the time difference to display in cell D5, type

=Timediff(D3,D4) into cell D5

You will need to format cell D5 to the time format for it to display correctly.

for info the "n" in the Datediff function is for Minutes and 1440 is obviously the number of minutes in a day.

PM me if you need help in adding a module as I don't know your experience level in VB and don't want to tell you how to suck eggs. ;)

MAx

[Tinstaafl]

Hiya RT

Can't email at the moment - Micro$hit OE is having hissy fit tonight so here it is in a nutshell:


Format the Beginning Time & Ending Time cells as a time format.

In the Time Interval column use the following formula


=IF(b1<a1,b1+1,b1)-a1


Cell 'A1' is beginning time

Cell 'B2' is ending time

These two cells and the time interal are formatted in ordinary 24:00 hr.

The only catch seems to be that the data entry by the users must use a colon ':' to separate hrs & minutes.

Hope it works OK for you.

T.

[Rollingthunder]

Thanks all, especially Tinstaafl

Works a treat.

Straightforward yet elegant.