Ask Your Question
1

Calculating night time working hours (military)

asked 2021-01-04 11:35:14 +0100

alekc gravatar image

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

edit retag flag offensive close merge delete

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2021-01-04 11:53:25 +0100 )edit

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.

alekc gravatar imagealekc ( 2021-01-04 12:21:02 +0100 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2021-01-04 12:24:56 +0100

PKG gravatar image

updated 2021-01-04 13:51:18 +0100

See here.

C:\fakepath\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.

edit flag offensive delete link more

Comments

1

Perfekt! Works exactly as intended. Thank you!

alekc gravatar imagealekc ( 2021-01-04 13:29:18 +0100 )edit
1

answered 2021-01-04 12:30:02 +0100

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
edit flag offensive delete link more

Comments

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

alekc gravatar imagealekc ( 2021-01-04 13:28:47 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-04 11:35:14 +0100

Seen: 51 times

Last updated: Jan 04