Excel Spreadsheet Logbook

[Pilot Pete]

Well, my searches have brought up only one post on the subject of how to write a formula to add up hours and minutes in excel to create a logbook. See here This however is addressing a little more advanced problem than I have.

So, can anyone point me (someone who has never written an Excel formula before!) to a idiots guide on how to format sub-total and total cells for adding up hours and minutes? So that I can have running sub-totals (like each page of a logbook) and a grand total?

I thank you in advance.

PP

[ORAC]

To hear is to obey.

[Pilot Pete]

Orac,

You are a gentleman.

Many thanks.

PP

[Pilot Pete]

Gentleman you may be, but having just tried to follow the instructions provided it does not seem to work!

Can I use a formula to convert to integrals of 60 therefore just straight summing of figures. Using a 'time' formula to format the cells does not allow me to add up hundreds of hours.

Any boffins got an answer?

Thanks

PP

[Tinstaafl]

This works for me in a simple spreadsheet I made for work to calculate flight times from wheels off to wheels on. It sums the individual flight times as well.

Wheels off & on cells (A1 & B1 respectively): format as time eg 13:30
Sector time (C1): format as custom [h]:mm
formula is: =IF(b1<a1,b1+1,b1)-a1
Total times (cell C10): format as custom [h]:mm
formula is: =sum(C1-C9)

Note: You must enter the times with a colon divisor eg 14:34, not 14.34.

The sector time works as follows:

=IF(wheels on < wheels off, wheels on+1, wheels on)-wheels off.

Translated: IF 'wheels on' is less than 'wheels off' add to it 24 hrs (a single day ie '1') then subtract 'wheels off' from 'wheels on'. If 'wheels on is NOT less than 'wheels off' then subtract without doing the previous 'add a day' thing.

This correctly calculates the time even if the arrival time is after midnight. An arrival time after midnight means the time is numerically less than the departure time. It's not really since the time has moved forward by a day. Excel stores date/times as a whole number+decimal fraction. The whole number is days, the decimal part is the hour/minute/second fraction of a whole day.

Starting on day '0', 12 midday would be 0.5, a day later 1.5, 2 days later at 6pm would be 2.75 etc etc.

If you want to split the hours & minutes for some other manipulation then you can use the =HOUR(cell) & =MINUTE(cell) functions. These two refer only to the numerical value of the hour or minute component of a time.

You could then use the minutes fraction to derive a decimal fraction of an hour, then add the decimal back to the hour fraction. There's probably a function in Excel to to that directly since it already stores time as decimals.

You'd still have to use the ROUND function to round to the nearest tenth. Something like

=(hour(D1))+(round((minutes(D1)/60*100),1))

I've not tested this but it would be something similar. I've used something like it to calculate the expected fuel burn for the sector.

[ORAC]

Works for me.

Select the column. Go to Format/Cell/Custom and select the format hh:mm.
Select the cell you want the total to appear in and, using Custom, enter the format [hhh]:mm.

Total the cells to be added using either autosum or the formula =SUM(G2:G19), where G2 and G19 are the first and last cells to be totalled.

I just did it, entered the figures below and got the total as displayed.

08:15
07:12
02:11
02:15
01:15
12:15
11:00
08:15
05:15
22:15
22:15
22:15
124:38

[Pilot Pete]

Thanks again chaps, I'll give it a go.

PP