Calculate Work Hours with night shift and overtime

Question

Calculate Work Hours with night shift and overtime

Im not that good with numbers, I can only do simple things but I'd like to learn/try to make something better..

Sorry for my broken English 🤣

my shift starts 23:00 and ends at 8:00.

Here in Japan Night Shift starts at 22:00 and ends at 5:00... Sometimes I have to get in early (For example 21:00)

Normal worked time are the first 8 hours.. after that is overtime... but in the middle of this, between 22:00-5:00 I receive 25% more due to the night shift... and have to remove 1 hour everyday from my break time...

And sometimes I work only 6/7h a day...

It is kinda messy...

I'd like to write my entrance and exit time and receive the others informations to be able to calculate how much I am going to receive..

Show me the "Normal Hours", "Night Shift", "Overtime" and "Total of worked hours"... I tried a lot of things and I was able to know the total but I dont know how to do to show the others... and when I have blank spaces on those days I havent worked yet... it keeps showing me an error triangle it is annoying and wont let me =SUM the amount of hours from the entire month...

I dont know what to do to dont show those errors when my entrance/exit are still blank...

Example:

Posted on Feb 8, 2020 10:43 AM

Reply

Answer 1

SGIII

Level 8

37,804 points

Feb 10, 2020 6:08 AM in response to りさちゃん

This is not "beginning" Numbers! It's actually a very hard problem. You could try something like this:

See this example (Dropbox download).

Note the hours are given in decimal values so that you can multiply them by pay rates.

This (I think) works reliably for your situation, where your shifts cross midnight. But it would need modifications if you were to change to primarily shifts where you are working during the day.

SG

Reply

Answer 2

Feb 10, 2020 2:16 PM in response to SGIII

that helped a lot, thx!

now, what to do to show a blank space... when theres no IN and OUT time?

Used your example to show you what I mean...

Reply

Answer 3

SGIII

Level 8

37,804 points

Feb 10, 2020 11:28 PM in response to りさちゃん

I've added tables to the example posted at the link so you can see one way to repress the warning triangles.

Reviewing my original suggested formula I saw an error in the results for 23:00 to 07:00 shift. Less the 1-hour break the night hours should be 5, not 6.

That led to research on the web and this following long, ugly adaptation of an Excel formula that seems to work correctly, both for night shift and day shifts:

=IFERROR(MAX(0,MOD(IF(B2>C2,MIN(TIMEVALUE(C2),TIMEVALUE(Shifts::$B$3))−MAX(TIMEVALUE(B2),TIMEVALUE(Shifts::$B$2)),MAX(0,TIMEVALUE(Shifts::$B$3)−TIMEVALUE(B2))+MAX(0,TIMEVALUE(C2)−TIMEVALUE(Shifts::$B$2))),1)*24−Shifts::$B$4),"")

See the tables on the "Generalized..." sheet if you want to see how the Excel monster was tamed for use in Numbers.

This formula is used in the tables marked "revised" in the example file. I think the "Revised - blank rows no warning" table is closest to what you want.

Unlike with the original formula, "times" can now be entered in cells formatted as Text or, if you want, Automatic or Date & Time.

SG

Reply

Calculate Work Hours with night shift and overtime

Similar questions