logbook night calculations in Excel?

[CK2]

Does anyone have an idea how to calculate the night part of a flight in Excel?

If anyone knows the formula for it or a place where it can be found, I would very much appreciate it if you could post it here!

It doesn't necessarily have to be the formula in the "Excel" way, I think I could manage to transform it into that format somehow.

Thanks a lot!

[Lear_doctor]

Tinstaafl posted this recently. It apears to to take into consideration the change from one day to the next. Is this what you needed?


Regards

The Doc


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.


[Last edited by Tinstaafl on 17th October 2003 at 19:58]

[Tinstaafl]

What do you mean by 'calculate the night part of a flight'? It doesn't need calculating. It's whatever you note down as the duration spent flying during darkness.

Do you mean calculate a duration that begins before midnight & finishes after midnight? If so do a search - it's been covered a couple of times before on this forum.

edit: ooops....someone searched & found! :O

[fobotcso]

There are dark forces at work here. Is it those Chem Trails again?

How did Tinstaafl manage to edit Lear_doctor's thread a month before he posted it? :suspect:

[CK2]

Hi again!

Thanks Doc, but that isn't what I'm looking for.

What you mentioned is the easy part - unfortunately.

And thanks for your advice Tinstaafl! I might a bit retarded in detail but not in general... :D
I do know how to spell "search" and how to do it, but whatever words and combinations I used, nothing productive came up. I even used operators in my search (well, heck, what a miracle). ;)

No, really, what I want is a bit stupid perhaps, I know. I don't want to sit in the plane and look on the watch every time I see the sun set or rise and then calculate what the night part of the whole flight was.

And yes, I mean that part when it's dark outside, or in the cockpit, whatever.

Quite a few logbook programs do that automatically when entering the departure and destination airfield combined with the times. It's just one of those not really necessary things that some people worry about. I'm one of them it seems.

So, instead of doing guesswork all the time I would simply like to have a calculation in my spreadsheet that tells me this dark time.

Still any helpers available?