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.
Thanks in advanced.
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 | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
1 | Scheduled | On Premise | Days | |||
2 | 2 | 0 | DA5C0600 | CAN FC Sun-Wed 10 hr 0600 | ||
3 | 2 | 0 | DA5C0630 | CAN FC Sun-Wed 10 hr 0630 | ||
4 | 18 | 0 | DA5C0700 | CAN FC Sun-Wed 10 hr 0700 | ||
5 | 15 | 8 | DB1C0700 | FC Wed-Sat 10 hr 0700 | ||
6 | 8 | 0 | DF6C0645 | CAN FC Mon-Thu 10hr 0645 | ||
7 | 8 | 0 | DF6C0700 | CAN FC Mon-Thur 10 hr 0700 | ||
Scheduled |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B7 | B2 | =IF($D2="","",COUNTIFS(Roster!$M:$M,$D2)) |
C2:C7 | C2 | =IF($D2="","",COUNTIFS('On Premise'!$E:$E,$D2,'On Premise'!$H:$H,"X")) |
Thanks in advanced.