Calculating (extracting) hours between two times

[pointman787]

I have made table for calculating hours and it is working pretty fine. But, I want to extract night time e.g. night hours during specific START and END time. And there is no need to make difference between weekdays and weekends.

C6 is START TIME formatted as [h]:mm,
E6 is END TIME formatted as [h]:mm,
F6 is TOTAL TIME formatted as [h]:mm with formula =SUM(E6-C6+(C6>E6)*1) to calculate times passing midnight.

So my wish is to establish column H (also formatted as [h]:mm) to extract night hours, if any, between C6 and E6.

Any tips would be highly appreciated and I am thanking in advance.

[pointman787]

I am new here so I forgot that it is easier if I attached specific sheet.

Thanks guys.

[pointman787]

More details which might help....
I want to establish precise time in format [h]:mm in column C6 and E6 so that night time would be extract in column H.
It is a question of calculating pilot's flight time and of course night "flight" time which has higher pay scale.
I can't establish formula so that excel recognize night part during particular flight. It is not night shift only "flying during night".

I will be grateful, thx...

[shg]

What hours are "night?"

[pointman787]

It will be changed for every month, because for pilots every day has different defined night time.......
But to make things simpler I will defined for every month.
Let's say, for test purposes, from 22:00 to 05:00.

[pointman787]

Anybody ???

[pointman787]

In field H6 I put formula which I found on this forum:

IF(E6=;";IF(E6<C6;MIN(E6;1/4)+(1-MAX(C6;7/8));IF(C6<1/4;MIN(1/4;E6)-C6;0)+IF(E6>7/8;E6-7/8;0)))

but it comes with FALSE.

Any suggestions....

[pointman787]

Nobody ????? (((((

[Toonies]

Hi try this

=IF(COUNT($C6,$E6)=2,MAX(0,MIN($E6+($C6>$E6))-MAX($C6)),0)

Or this

=MOD(E6-IF(C6 < TIME(22,0,0),TIME(22,0,0),C6),1)

place in F6

[pointman787]

Thanks Toonies but it's not working.
I see the point of your formula.
H6 has to calculate time if in C6 is more than 22:00 and in E6 is less than 05:00 trough the midnight.
I will keep trying, but thank you very much

[pointman787]

I need formula for H6, although those two are incorrect.

[pointman787]

=IF(C6>TIME(22;0*E6>TIME(5;0;SUM(E6-C6)+(C6>E6)*1)

What I have done by far, resulted with this formula in column H.
But, if the time in column C and column E is not between 22:00 and 05:00 I'm getting the same result like in column F, which is not correct.

Any help is very worm welcome.

[shg]

See attached.

[pointman787]

Thanks shg, very very very good.

But I will not put SOLVED yet because I want to to fine-tunning e.g. I want to remove "night beg" and "night end".
And one more thing. Can You please explain me this =RANDBETWEEN(0; 287)/288. Because if I will use it I have to change a lot of columns. Further more, I saw if I changed back to [h]:mm everything working normally.
But once again, thank you very much.

[shg]

Can You please explain me this =RANDBETWEEN(0; 287)/288.

Random input data.

[pointman787]

I see...but if I changed to [h]:mm everything seems to be fine. And also, those names of columns if I changed to A,B,C,D....... seems to be working fine.
Do you think that it will go crazy with those changes ?

[Pepe Le Mokko]

To be on the safe side ( if start and end time are 24hrs apart) I would suggest =(E6-C6+(C6>=E6)) (the SUM function is redundant)

[pointman787]

Hi, Pepe,
Thanks. Formula for BLOCK HRS working fine, the problem was NIGHT HRS before SHG helped with his suggestion.
One way or another, I have to have field for NIGHT START and NIGHT END due to calculation of night for the pilots. Normally it is different for every day (and every airport) and in UTC time, but for me it will do to make interpolation for every month.

For me??? It will have to work for them...... I am signing the salaries..........

[Pepe Le Mokko]

Things would be much easier if you entered date AND time one way or another in departure and arrival and the SUM function is still redundant :-)))

[pointman787]

That is the point, I don't want to enter date and time. I know it would be much easier. And that is another question of log book entries........... long story.....
But any way, thanks. When In will finish it, I will put finished sheet on forum.

[pointman787]

The problem is solved. The result is within attached sheet.
Data in the sheet are interpolated values for JAN and FEB 2012 for LOWW (Vienna, Schwechat) in UTC.
Thanks everybody.

[JLPF]

The problem is solved. The result is within attached sheet.
Data in the sheet are interpolated values for JAN and FEB 2012 for LOWW (Vienna, Schwechat) in UTC.
Thanks everybody.

Hi. I am on the other side.

The time between the departure and arrival, with your sheet, gives me the night time, but you are assuming that the departure is always LOWW. To have a correct logbook I need to calculate the night time during my flight, that is, all these parameters change: day, departure airport with its associated sunset and arrival airport with its associated sunrise.

I have the tables for sunset and sunrise, for example, Madrid 2004.xlsx

Of course I have the tables for all other destinations and departure airports. So having already in my log the date, airports and times, how can I link the information on the sheets like the example with your columns D and G? It will take me ages to include the sunset and sunrise in more than 8000 flights if I have to do it one by one (never had the need of night time before).

Thank you

[pointman787]

Hello JLPF,

Well I know what You are talking about because that is the reason for making my sheet in the first place.
My operations are 90% in Europe so I took Vienna because it is approximately in the center of Europe and on the year basis night hours will be more or less correct. Second reason why I did like that is the money. We have increased pay-scale for night flights and if I compare one pilot operating from Malta and other from Rovaniemi (which is on polar circle) making interpolated value for night I found my peace. Otherwise I would found myself in deeper problem.

But, to be sure, after finishing the sheet, I entered only night flights from last year and difference was 10% which is acceptable.

Further more, I was thinking about Your idea when I was asking for help on this forum but than I found application on the web for 10€ (iPhone of course) and it is already prepared for thinks You are asking about. My pilots downloaded and they saying it is a classic log book with pre-entered data for every airport including NIGHT.

So my suggestion is, download and enjoy. Because, Your sheet would be very much complicated and huge. But if my will do the job let me know I can send You one for all year.

God Speed,

[JLPF]

Thank you for your suggestions.

Two main concerns. First one is the amount of information that I have already logged. There are many logs up there that calculate the night time automatically, but no one of them will accept importing my data, why? Perhaps they were prepared just to type data, not to import it, although I am in contact with the programmer of one of them.
Secondly, there are also many programms, even for free, that calculate the night time very accurately, but you have to feed the info one by one.

My excel was absolutely private, for own use, with my own needs, that is why it does not fit with those in the market.

I will keep working!