Formula - Sum worked hours based on shift timing

[gd6noob]

Hi guys.

I have this Hour Summary table but requires further tweaking and I tried a few methods from internet but it doesnt seem to work.
As you can see below, I have 2 Summary tables, 1 for day shift and 1 for night shift.

For the Day Shift Summary table, D8, E8 and F8 cell should only pick up people who signed in for SOS (column J) before 18:00.
For the Night Shift Summary table, D18, E18 and F18 cell should only pick up people who signed in for SOS (column J) on or after 18:00.

Green Badge Sign-In Sheet.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2									Sunday	Please enter in 24hour format
3									Login	SOS	Break Out	Break In	EOS	Total Hous
4									Name 1
5									Name 2
6			Day Shift Summary						Name 3
7			Day Shift	HC Onsite	HC Break	Total Hours			Name 4
8			Sunday	2	0				Name 5	7:00	13:01	13:31	17:30	10:00
9			Monday	0	0				Name 6
10			Tuesday	0	0				Name 7
11			Wednesday	2	1				Name 8
12			Thursday	0	0				Name 9	7:00	13:01	13:31	17:30	10:00
13			Friday	0	0				Name 10
14			Saturday	0	0				Name 11
15									Name 12	18:00
16			Night Shift Summary						Name 13	20:00
17			Night Shift	HC Onsite	HC Break	Total Hours			Name 14
18			Sunday						Name 15
19			Monday
20			Tuesday
21			Wednesday
22			Thursday
23			Friday
24			Saturday
Week 44

Cell Formulas
Range	Formula
D8	D8	=COUNTIFS(J3:J28,">0",M3:M28,"",K3:K28,">0",L3:L28,">0") + COUNTIFS(J3:J28,">0",M3:M28,"",K3:K28,"",L3:L28,"")
E8	E8	=COUNTIFS(J3:J28,">0",M3:M28,"",K3:K28,">0",L3:L28,"")
D9	D9	=COUNTIFS(C31:C56,">0",F31:F56,"",D31:D56,">0",E31:E56,">0") + COUNTIFS(C31:C56,">0",F31:F56,"",D31:D56,"",E31:E56,"")
E9	E9	=COUNTIFS(C31:C56,">0",F31:F56,"",D31:D56,">0",E31:E56,"")
D10	D10	=COUNTIFS(J31:J56,">0",M31:M56,"",K31:K56,">0",L31:L56,">0") + COUNTIFS(J31:J56,">0",M31:M56,"",K31:K56,"",L31:L56,"")
E10	E10	=COUNTIFS(J31:J56,">0",M31:M56,"",K31:K56,">0",L31:L56,"")
D11	D11	=COUNTIFS(C59:C84,">0",F59:F84,"",D59:D84,">0",E59:E84,">0") + COUNTIFS(C59:C84,">0",F59:F84,"",D59:D84,"",E59:E84,"")
E11	E11	=COUNTIFS(C59:C84,">0",F59:F84,"",D59:D84,">0",E59:E84,"")
D12	D12	=COUNTIFS(J59:J84,">0",M59:M84,"",K59:K84,">0",L59:L84,">0") + COUNTIFS(J59:J84,">0",M59:M84,"",K59:K84,"",L59:L84,"")
E12	E12	=COUNTIFS(J59:J84,">0",M59:M84,"",K59:K84,">0",L59:L84,"")
D13	D13	=COUNTIFS(C87:C112,">0",F87:F112,"",D87:D112,">0",E87:E112,">0") + COUNTIFS(C87:C112,">0",F87:F112,"",D87:D112,"",E87:E112,"")
E13	E13	=COUNTIFS(C87:C112,">0",F87:F112,"",D87:D112,">0",E87:E112,"")
D14	D14	=COUNTIFS(J87:J112,">0",M87:M112,"",K87:K112,">0",L87:L112,">0") + COUNTIFS(J87:J112,">0",M87:M112,"",K87:K112,"",L87:L112,"")
E14	E14	=COUNTIFS(J87:J112,">0",M87:M112,"",K87:K112,">0",L87:L112,"")
F24	F24	=IF(SUM($N$88:$N$112)=0,"",SUM($N$88:$N$112))
N4:N24	N4	=IF(M4="","",IF(J4>M4,M4+1,M4)-J4-(L4-K4))

Thanks for the help.

 

[Toadstool]

Hi Gd6noob,

I'm struggling to follow why the other columns are checked for greater than zero or null so let me just answer your basic question.

SOS before 6pm is

=COUNTIFS($J$4:$J$28,"<"&TIME(18,0,0))

SOS on or after 6pm is =

COUNTIFS($J$4:$J$28,">="&TIME(18,0,0))

 

[gd6noob]

Hi Gd6noob,

I'm struggling to follow why the other columns are checked for greater than zero or null so let me just answer your basic question.

SOS before 6pm is

=COUNTIFS($J$4:$J$28,"<"&TIME(18,0,0))

SOS on or after 6pm is =

COUNTIFS($J$4:$J$28,">="&TIME(18,0,0))

Hi Toadstool,

Thank you for responding.

For column N, the reason is, if someone enters the SOS, then break time and no EOS time, then the total hours will be counting and giving incorrect hours, so therefore, I will keep this column blank until there is an EOS time aswell. This is the only way i can figure this out.

Same goes for cells in the Summary table. Im not very proficient with formulas and this is the best solution I can think of. These cells also count the current active headcounts during onsite or on break. If someone has SOS and then clocks out for break, in D8, the count would be -1 and E8 would be +1. As you can see, on the Sunday, there are currently 2 people who are clocked in, which reflects correct in the Shift Summary but I would need the count to be separated between day and night shift.

Thank you

 

[Toadstool]

So has the question been answered?

You should know that Excel holds time as a fraction of a day. 18:00 is three quarters through a day so instead of TIME(18,0,0) you could also use 0.75 for that check... so for the Night Shift, HC Onsite you could use

=COUNTIFS(J3:J28,">=0.75",M3:M28,"",K3:K28,">0",L3:L28,">0") + COUNTIFS(J3:J28,">=0.75",M3:M28,"",K3:K28,"",L3:L28,"")

which gives the number 2 on your test data.
The Day Shift HC Onsite would be

=COUNTIFS(J3:J28,"<0.75",M3:M28,"",K3:K28,">0",L3:L28,">0") + COUNTIFS(J3:J28,"<0.75",M3:M28,"",K3:K28,"",L3:L28,"")

but that returns a zero because neither COUNTIFS satisfy the other conditions.

If I could also make a comment on the day of the week design. I don't know if the data in columns I:N are imported or typed in but if you could repeat the day of the week on each data row, or even better the date, then that would prevent you having to manually change the cell addresses, which is a dangerous approach. Using the actual date would also simplify the weekly Day/Night Shift summaries as each would just need a "Week Commencing" date then the COUNTIFS would include a >= that date and <= that date plus 7. Just a thought.

 

[gd6noob]

So has the question been answered?

You should know that Excel holds time as a fraction of a day. 18:00 is three quarters through a day so instead of TIME(18,0,0) you could also use 0.75 for that check... so for the Night Shift, HC Onsite you could use

=COUNTIFS(J3:J28,">=0.75",M3:M28,"",K3:K28,">0",L3:L28,">0") + COUNTIFS(J3:J28,">=0.75",M3:M28,"",K3:K28,"",L3:L28,"")

which gives the number 2 on your test data.
The Day Shift HC Onsite would be

=COUNTIFS(J3:J28,"<0.75",M3:M28,"",K3:K28,">0",L3:L28,">0") + COUNTIFS(J3:J28,"<0.75",M3:M28,"",K3:K28,"",L3:L28,"")

but that returns a zero because neither COUNTIFS satisfy the other conditions.

Thank you, this is working for me.

If I could also make a comment on the day of the week design. I don't know if the data in columns I:N are imported or typed in but if you could repeat the day of the week on each data row, or even better the date, then that would prevent you having to manually change the cell addresses, which is a dangerous approach. Using the actual date would also simplify the weekly Day/Night Shift summaries as each would just need a "Week Commencing" date then the COUNTIFS would include a >= that date and <= that date plus 7. Just a thought.

Sorry not sure I follow?
In the sample table, cells for Login, SOS, Break and EOS times are manually entered by each persons and all other cells are locked from editing.

 

[Toadstool]

"Sorry not sure I follow?"
I was a little concerned that for each day of the week you change the target cell range which means it's very easy (if a row is inserted) to end up picking up the wrong data.

If you can have each row contain the day or date then you can use the same formula for every cell. e.g.

Gd6noob-2.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2							Sunday	Please enter in 24hour format
3							Login	SOS	Break Out	Break In	EOS	Total Hous
4							Name 1						Sunday
5							Name 2	8:00	12:00				Sunday
6	Day Shift Summary						Name 3	8:00	12:00	12:30			Sunday
7	Day Shift	HC Onsite	HC Break	Total Hours			Name 4	8:00	12:00	12:30			Sunday
8	Sunday	2	1				Name 5	7:00	13:01	13:31	17:30	10:00	Sunday
9	Monday	0	0				Name 6						Sunday
10	Tuesday	0	0				Name 7						Sunday
11	Wednesday	0	0				Name 8						Sunday
12	Thursday	0	0				Name 9	7:00	13:01	13:31	17:30	10:00	Sunday
13	Friday	0	0				Name 10						Sunday
14	Saturday	0	0				Name 11						Sunday
15							Name 12	18:00					Sunday
16	Night Shift Summary						Name 13	20:00					Sunday
Sheet1 (3)

Cell Formulas
Range	Formula
N4:N16	N4	=IF(M4="","",IF(J4>M4,M4+1,M4)-J4-(L4-K4))
O4:O16	O4	=$I$2
D8:D14	D8	=COUNTIFS($O$4:$O$9999,$C8,$J$4:$J$9999,"<0.75",$M$4:$M$9999,"",$K$4:$K$9999,">0",$L$4:$L$9999,">0") + COUNTIFS($O$4:$O$9999,$C8,$J$4:$J$9999,"<0.75",$M$4:$M$9999,"",$K$4:$K$9999,"",$L$4:$L$9999,"")
E8:E14	E8	=COUNTIFS($O$4:$O$9999,$C8,$J$4:$J$9999,"<0.75",$M$4:$M$9999,"",$K$4:$K$9999,">0",$L$4:$L$9999,"")

 

[Toadstool]

...and if you want to try my second suggestion of having the date on each row then it may look something like this:

Gd6noob-2.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
1
2							Sunday	Please enter in 24hour format
3						Date	Login	SOS	Break Out	Break In	EOS	Total Hous
4						31-Oct-21	Name 1
5						31-Oct-21	Name 2	8:00	12:00
6	Day Shift Summary for w/c			31-Oct-21		31-Oct-21	Name 3	8:00	12:00	12:30
7	Day Shift	HC Onsite	HC Break	Total Hours		31-Oct-21	Name 4	8:00	12:00	12:30
8	Sunday	2	1			31-Oct-21	Name 5	7:00	13:01	13:31	17:30	10:00
9	Monday	1	2			01-Nov-21	Name 6	8:00	12:00
10	Tuesday	0	0			01-Nov-21	Name 7	8:00	12:00
11	Wednesday	0	0			01-Nov-21	Name 8	8:00	12:00	12:30
12	Thursday	0	0			01-Nov-21	Name 9	7:00	13:01	13:31	17:30	10:00
13	Friday	0	0			01-Nov-21	Name 10
14	Saturday	0	0			01-Nov-21	Name 11
Sheet1 (4)

Cell Formulas
Range	Formula
C8	C8	=$F$6
D8:D14	D8	=COUNTIFS($H$4:$H$9999,">="&$C8,$H$4:$H$9999,"<="&$C8,$J$4:$J$9999,"<0.75",$M$4:$M$9999,"",$K$4:$K$9999,">0",$L$4:$L$9999,">0") + COUNTIFS($H$4:$H$9999,">="&$C8,$H$4:$H$9999,"<="&$C8,$J$4:$J$9999,"<0.75",$M$4:$M$9999,"",$K$4:$K$9999,"",$L$4:$L$9999,"")
E8:E14	E8	=COUNTIFS($H$4:$H$9999,">="&$C8,$H$4:$H$9999,"<="&$C8,$J$4:$J$9999,"<0.75",$M$4:$M$9999,"",$K$4:$K$9999,">0",$L$4:$L$9999,"")
C9:C14	C9	=C8+1
N4:N14	N4	=IF(M4="","",IF(J4>M4,M4+1,M4)-J4-(L4-K4))