Excel - Count employees scheduled based on day

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
170
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

Hope anyone can help.

What Im trying to do is count the number of people that is supposed to be scheduled according to their shift based on today's day of the week.

As you can see below, we have multi shifts that start on different days. Currently the Scheduled column is wrong because today is Friday, and shifts that work Sun-Wed is being counted.
Wanting to know if there is a way to count this accurately and automate as much as possible.
If VBA would be easier, then I will be open for this route.

Roster and Attendance.xlsb
BCDE
1ScheduledOn PremiseDays
220DA5C0600CAN FC Sun-Wed 10 hr 0600
320DA5C0630CAN FC Sun-Wed 10 hr 0630
4180DA5C0700CAN FC Sun-Wed 10 hr 0700
5158DB1C0700FC Wed-Sat 10 hr 0700
680DF6C0645CAN FC Mon-Thu 10hr 0645
780DF6C0700CAN FC Mon-Thur 10 hr 0700
Scheduled
Cell Formulas
RangeFormula
B2:B7B2=IF($D2="","",COUNTIFS(Roster!$M:$M,$D2))
C2:C7C2=IF($D2="","",COUNTIFS('On Premise'!$E:$E,$D2,'On Premise'!$H:$H,"X"))


Thanks in advanced.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Can you post the other worksheets Om Premise and Roster? Also, is there any chance you can expand column E into multiple columns which would show the start and end day?
 
Upvote 0
Can you post the other worksheets Om Premise and Roster? Also, is there any chance you can expand column E into multiple columns which would show the start and end day?
The On Premise and Roster just had Names, Employee ID and their Shift code (column D)

The issue with Column E, is that it can be in a few ways, if you look at cell E6 and E7. I couldnt get figure out the formula to extract this properly.


I found a temp solution, creating a tab for each day and adding the shift that falls on those days. Then doing the calcualation.. probably not the best way.
 
Upvote 0
Book1
BCDE
1ScheduledOn PremiseDays
20#VALUE!DA5C0600CAN FC Sun-Wed 10 hr 0600
30#VALUE!DA5C0630CAN FC Sun-Wed 10 hr 0630
40#VALUE!DA5C0700CAN FC Sun-Wed 10 hr 0700
511#VALUE!DB1C0700FC Wed-Sat 10 hr 0700
60#VALUE!DF6C0645CAN FC Mon-Thu 10hr 0645
70#VALUE!DF6C0700CAN FC Mon-Thur 10 hr 0700
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=IFS($D2="","",AND(MATCH(TRUE,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}=TEXT(TODAY(),"ddd"),0)>=MATCH(TRUE,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}=MID($E2,FIND("-",$E2,1)-3,3),0), MATCH(TRUE,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}=TEXT(TODAY(),"ddd"),0)<=MATCH(TRUE,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}=MID($E2,FIND("-",$E2,1)+1,3),0)),COUNTIFS(Roster!$M:$M,$D2),TRUE,0)
C2:C7C2=IF($E2="","",COUNTIFS('On Premise'!$E:$E,$E2,'On Premise'!$H:$H,"X"))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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