Sum night shift working hours

[Boris Josipovic]

Hi.

So lets see. My example contains, for each day of the month (June 2022), the start and end time of example worker's shift. So, 07 14 means he worked 7 hours, but 21 07 means he worked night shift (10 hours).

What I'd like are the following formulas:

1) Total hours in day shift
2) Total hours in night shift
3) Total hours - Sundays only
3) Total night shift hours on Sundays

In fact, it's Sunday and I'm working night shift now.


Thanks very much in advance.

[Boris Josipovic]

OK, I've done half of work:

1) Total hours in day shift:

=SUMPRODUCT((LEFT(C4:AD4;2)<RIGHT(C4:AD4;2))*1;-IFERROR(VALUE(LEFT(C4:AD4;2));0)+IFERROR(VALUE(RIGHT(C4:AD4;2));0))

2) Total hours in night shift

=SUMPRODUCT((LEFT(C4:AD4;2)>RIGHT(C4:AD4;2))*1;24-IFERROR(VALUE(LEFT(C4:AD4;2));0)+IFERROR(VALUE(RIGHT(C4:AD4;2));0))


Both are array formulas (Ctrl + Shift + Enter)

[Boris Josipovic]

Wow, I solved it myself:

3) Total hours - Sundays only

=SUMPRODUCT(IFERROR(IF(VALUE(LEFT(C4:AG4;2))<VALUE(RIGHT(C4:AG4;2));VALUE(RIGHT(C4:AG4;2))-VALUE(LEFT(C4:AG4;2));24+VALUE(RIGHT(C4:AG4;2))-VALUE(LEFT(C4:AG4;2)));0)*(IFERROR(WEEKDAY(DATE(2022;6;C3:AG3);11);0)=7))

4) Total night shift hours on Sundays

=SUMPRODUCT(IFERROR(IF(VALUE(LEFT(C4:AG4;2))<VALUE(RIGHT(C4:AG4;2));VALUE(RIGHT(C4:AG4;2))-VALUE(LEFT(C4:AG4;2));24+VALUE(RIGHT(C4:AG4;2))-VALUE(LEFT(C4:AG4;2)));0)*(IFERROR(WEEKDAY(DATE(2022;6;C3:AG3);11);0)=7)*(LEFT(C4:AG4;2)>RIGHT(C4:AG4;2)))


Array formulas (Ctrl + Shift + Enter)



Cheers to the good people who read my problem.