Calculating night time working hours (military)

[alekc]

Hello,

I’m working on a spreadsheet which would automatically calculate the amount of night hours worked.

Night hours are calculated militarily from 22:00 until 6:00 in the morning. It’s a spreadsheet which other people are using, so I’d like “6:00” to be inputted as such and not “30:00” even if it’s finally formatted as “6:00”.

The example result I’d like to achieve is:

From: 21:00

To: 7:00

Night: 8:00

The problem I’m having is how to qualify the night hours.

Thanks in advance for any pointers,
Alex

[mikekaganski]

so I’d like “6:00” to be inputted as such and not “30:00”

It would better (much better!) be entered as “2021-01-04 6:00”. Having “time” without date is not a point in time, but just some meaningless characters.

Of course, you can also combine “date” from some place with “time” in another, to get the points in time for further processing.

And yes, this is unrelated to the task of finding only night hours in some time interval. That’s why I’m putting it to comment.

[alekc]

Thanks for the pointer.
The problem I’m having is how to arrive at this result. Rather than having the user input the date and time in a cell, I’d have to add the time to the date. But I don’t know how to calculate that the time past midnight should add a day.

[PKG]

See here.

night working time.ods

---

If this answers your question, please click on the circle with the tick () on the left (the point is highlighted in green and the answer is highlighted in color) so that other users can see that the question has been answered.

[alekc]

Perfekt!
Works exactly as intended.
Thank you!

[mikekaganski]

Given two datetimes in A1 and B1, like

       A1                B1
2021-01-04T03:00  2021-01-08T23:00

the following formula calculates the night hours of the period (works for periods in the same day, or over several days; only counts hours, not fractions of hours - expects minutes/seconds be zero for “simplicity”):

=MAX(6-HOUR(A1);0)+MIN(24-HOUR(A1);2)+MIN(HOUR(B1);6)+MAX(HOUR(B1)-22;0)+(INT(B1)-INT(A1)-1)*8

[alekc]

Also a valid solution, although the previous one fits the bill perfectly.
Thanks!