We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calculating night time working hours (military)

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

alekc gravatar image


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


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 +0200 )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 +0200 )edit

2 Answers

Sort by » oldest newest most voted

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

PKG gravatar image

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

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



Perfekt! Works exactly as intended. Thank you!

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

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

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"):

edit flag offensive delete link more


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

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

Question Tools

1 follower


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

Seen: 73 times

Last updated: Jan 04