Calculating working hours with night shift premiums

Here is one way:

Enter the full date/time value for the start and end in columns A and B. The "answers" will be in columns C and D. Columns E thru G are work columns which you can hide if you want.

G2=B2−A2

this is shorthand for... select cell G2, then type (or copy and paste from here) the formula:

=B2−A2

C2=G2−D2

D2=IF(E2>DURATION(0, 0, 0, 0,0), E2, DURATION(0, 0, 0, 0,0))+IF(F2>DURATION(0, 0, 0, 0,0), F2, DURATION(0, 0, 0, 0,0))

E2=((DATE(YEAR(A2), MONTH(A2), DAY(A2))&" 8:00 PM")−A2)

F2=(B2−(DATE(YEAR(B2), MONTH(B2), DAY(B2))&" 6:00 AM"))

select cells C2 thru G2, copy

select cells C2 thru the end of column G, paste

Hi papalapapp,

A challenging problem! This in not a solution, but a couple of ideas that may get us started.

With the Numbers Cell Data Format of Date & Time, we often find the "crossing midnight" problem (or feature).

I thought we might avoid that by calculating the number of hours since midnight on each day. This requires that start and end times be entered on the actual day.

In Table 1, enter a Start time such as 5:00pm on the day the shift starts. Enter the End time on the day the shift ends.

Columns A and B are formatted to display the full Date & Time after pressing enter.

Formula in C2 (and Fill Down)

TIMEVALUE(A2)×24

Formula in D2 (and Fill Down)

=TIMEVALUE(B2)×24

That shows the (decimal fractions) of the Start and End times (24 hour clock) independent of the Date.

Actual Hours (E2 and Fill Down)

=B2−A2

Where I got stuck is in Column F (Night Shift Hours) but here is an idea. Another table as an "Admin" table (hidden on another Sheet)

Over to others to follow on 🙂.

Regards,

Ian.

Hello

You may try something like this.

Table 1 (exceprt) A1 time in A2 2015-03-02 17:00:00 A3 2015-03-04 01:00:00 A4 2015-03-06 20:00:00 B1 time out B2 2015-03-02 22:00:00 B3 2015-03-04 10:00:00 B4 2015-03-07 03:00:00 C1 x C2 =TIMEVALUE(A2) C3 =TIMEVALUE(A3) C4 =TIMEVALUE(A4) D1 y D2 =TIMEVALUE(A2)+DUR2DAYS(B2-A2) D3 =TIMEVALUE(A3)+DUR2DAYS(B3-A3) D4 =TIMEVALUE(A4)+DUR2DAYS(B4-A4) E1 a E2 =6/24 E3 =6/24 E4 =6/24 F1 b F2 =20/24 F3 =20/24 F4 =20/24 G1 dt G2 =MAX(MIN(F2,D2)-MAX(E2,C2),0)+MAX(MIN(F2+1,D2)-MAX(E2+1,C2),0) G3 =MAX(MIN(F3,D3)-MAX(E3,C3),0)+MAX(MIN(F3+1,D3)-MAX(E3+1,C3),0) G4 =MAX(MIN(F4,D4)-MAX(E4,C4),0)+MAX(MIN(F4+1,D4)-MAX(E4+1,C4),0) H1 nt H2 =D2-C2-G2 H3 =D3-C3-G3 H4 =D4-C4-G4 I1 day time I2 =DURATION(,G2) I3 =DURATION(,G3) I4 =DURATION(,G4) J1 night time J2 =DURATION(,H2) J3 =DURATION(,H3) J4 =DURATION(,H4) K1 total time K2 =I2+J2 K3 =I3+J3 K4 =I4+J4 L1 check L2 =D2-C2<=1 L3 =D3-C3<=1 L4 =D4-C4<=1

Notes.

Formulae in C2:L2 can be filled down.

Columns C:H and L are for internal use.

Column G calculates time in range [x, y] ∩ [a, b]; i.e., day time.

Column H calculates time in range [x, y] \ [a, b]; i.e., night time.

Column L checks a necessary condition of this method.

Table is built in Numbers v2.

Hope this may help,

H

Minor correction on comments. Notes on Columns G and H should have been:

Column G calculates time in range [x, y] ∩ ([a, b] ∪ [a + 1, b + 1]); i.e., day time.

Column H calculates time in range [x, y] \ ([a, b] ∪ [a + 1, b + 1]); i.e., night time.

Formulae in these columns in table are correct.

Regards,

H