Formula - Sum worked hours based on shift timing

G

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
170
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMN
2SundayPlease enter in 24hour format
3LoginSOSBreak OutBreak InEOSTotal Hous
4Name 1 
5Name 2 
6Day Shift SummaryName 3 
7Day ShiftHC OnsiteHC BreakTotal HoursName 4 
8Sunday20Name 57:0013:0113:3117:3010:00
9Monday00Name 6 
10Tuesday00Name 7 
11Wednesday21Name 8 
12Thursday00Name 97:0013:0113:3117:3010:00
13Friday00Name 10 
14Saturday00Name 11 
15Name 1218:00 
16Night Shift SummaryName 1320:00 
17Night ShiftHC OnsiteHC BreakTotal HoursName 14 
18SundayName 15 
19Monday 
20Tuesday 
21Wednesday 
22Thursday 
23Friday 
24Saturday  
Week 44
Cell Formulas
RangeFormula
D8D8=COUNTIFS(J3:J28,">0",M3:M28,"",K3:K28,">0",L3:L28,">0") + COUNTIFS(J3:J28,">0",M3:M28,"",K3:K28,"",L3:L28,"")
E8E8=COUNTIFS(J3:J28,">0",M3:M28,"",K3:K28,">0",L3:L28,"")
D9D9=COUNTIFS(C31:C56,">0",F31:F56,"",D31:D56,">0",E31:E56,">0") + COUNTIFS(C31:C56,">0",F31:F56,"",D31:D56,"",E31:E56,"")
E9E9=COUNTIFS(C31:C56,">0",F31:F56,"",D31:D56,">0",E31:E56,"")
D10D10=COUNTIFS(J31:J56,">0",M31:M56,"",K31:K56,">0",L31:L56,">0") + COUNTIFS(J31:J56,">0",M31:M56,"",K31:K56,"",L31:L56,"")
E10E10=COUNTIFS(J31:J56,">0",M31:M56,"",K31:K56,">0",L31:L56,"")
D11D11=COUNTIFS(C59:C84,">0",F59:F84,"",D59:D84,">0",E59:E84,">0") + COUNTIFS(C59:C84,">0",F59:F84,"",D59:D84,"",E59:E84,"")
E11E11=COUNTIFS(C59:C84,">0",F59:F84,"",D59:D84,">0",E59:E84,"")
D12D12=COUNTIFS(J59:J84,">0",M59:M84,"",K59:K84,">0",L59:L84,">0") + COUNTIFS(J59:J84,">0",M59:M84,"",K59:K84,"",L59:L84,"")
E12E12=COUNTIFS(J59:J84,">0",M59:M84,"",K59:K84,">0",L59:L84,"")
D13D13=COUNTIFS(C87:C112,">0",F87:F112,"",D87:D112,">0",E87:E112,">0") + COUNTIFS(C87:C112,">0",F87:F112,"",D87:D112,"",E87:E112,"")
E13E13=COUNTIFS(C87:C112,">0",F87:F112,"",D87:D112,">0",E87:E112,"")
D14D14=COUNTIFS(J87:J112,">0",M87:M112,"",K87:K112,">0",L87:L112,">0") + COUNTIFS(J87:J112,">0",M87:M112,"",K87:K112,"",L87:L112,"")
E14E14=COUNTIFS(J87:J112,">0",M87:M112,"",K87:K112,">0",L87:L112,"")
F24F24=IF(SUM($N$88:$N$112)=0,"",SUM($N$88:$N$112))
N4:N24N4=IF(M4="","",IF(J4>M4,M4+1,M4)-J4-(L4-K4))


Thanks for the help.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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
Excel Formula: 
=COUNTIFS($J$4:$J$28,"<"&TIME(18,0,0))
SOS on or after 6pm is =
Excel Formula: 
COUNTIFS($J$4:$J$28,">="&TIME(18,0,0))
 
Upvote 0
Toadstool said:
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
Excel Formula: 
=COUNTIFS($J$4:$J$28,"<"&TIME(18,0,0))
SOS on or after 6pm is =
Excel Formula: 
COUNTIFS($J$4:$J$28,">="&TIME(18,0,0))
Click to expand...
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
 
Last edited:
Upvote 0
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
Excel Formula: 
=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
Excel Formula: 
=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.
 
Upvote 0
Solution
Toadstool said:
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
Excel Formula: 
=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
Excel Formula: 
=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.
Click to expand...
Thank you, this is working for me.

Toadstool said:
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.
Click to expand...
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.
 
Upvote 0
"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
CDEFGHIJKLMNO
1
2SundayPlease enter in 24hour format
3LoginSOSBreak OutBreak InEOSTotal Hous
4Name 1 Sunday
5Name 28:0012:00 Sunday
6Day Shift SummaryName 38:0012:0012:30 Sunday
7Day ShiftHC OnsiteHC BreakTotal HoursName 48:0012:0012:30 Sunday
8Sunday21Name 57:0013:0113:3117:3010:00Sunday
9Monday00Name 6 Sunday
10Tuesday00Name 7 Sunday
11Wednesday00Name 8 Sunday
12Thursday00Name 97:0013:0113:3117:3010:00Sunday
13Friday00Name 10 Sunday
14Saturday00Name 11 Sunday
15Name 1218:00 Sunday
16Night Shift SummaryName 1320:00 Sunday
Sheet1 (3)
Cell Formulas
RangeFormula
N4:N16N4=IF(M4="","",IF(J4>M4,M4+1,M4)-J4-(L4-K4))
O4:O16O4=$I$2
D8:D14D8=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:E14E8=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,"")
 
Upvote 0
...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
CDEFGHIJKLMN
1
2SundayPlease enter in 24hour format
3DateLoginSOSBreak OutBreak InEOSTotal Hous
431-Oct-21Name 1 
531-Oct-21Name 28:0012:00 
6Day Shift Summary for w/c31-Oct-2131-Oct-21Name 38:0012:0012:30 
7Day ShiftHC OnsiteHC BreakTotal Hours31-Oct-21Name 48:0012:0012:30 
8Sunday2131-Oct-21Name 57:0013:0113:3117:3010:00
9Monday1201-Nov-21Name 68:0012:00 
10Tuesday0001-Nov-21Name 78:0012:00 
11Wednesday0001-Nov-21Name 88:0012:0012:30 
12Thursday0001-Nov-21Name 97:0013:0113:3117:3010:00
13Friday0001-Nov-21Name 10 
14Saturday0001-Nov-21Name 11 
Sheet1 (4)
Cell Formulas
RangeFormula
C8C8=$F$6
D8:D14D8=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:E14E8=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:C14C9=C8+1
N4:N14N4=IF(M4="","",IF(J4>M4,M4+1,M4)-J4-(L4-K4))
 
Upvote 0
You must log in or register to reply here.

Similar threads

SteveP29
  • Solved
Golf Skins
Replies
3
Views
597
Fluff
Fluff
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
211
StephenCrump
S
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff
andrewb90
  • Question Question
Errors after midnight in formula calculation
Replies
4
Views
1K
AhoyNC
A
S
  • Question Question
Simplifying multiple formulae
Replies
2
Views
503
Stclements
S

Forum statistics

Threads
1,224,832
Messages
6,181,234
Members
453,026
Latest member
cknader

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top