CODEWITHSUNDEEP
excelexcel-formula
CityBlends
CityBlends

Reputation: 1

How do I count employees who work per hour including midnight using VBA?

I need to count the number of employee who work in a particular hour:

For ex: My shift is 11:00pm to 8:00am, and I'm looking for the number of employees who's working within 11:00pm to 12:00am. My current shift is within the provided time (1 hour). If I'm looking for 8:00am to 9:00am and my shift is not within the provided time, then no count for me.

The problem is the formula doesn't counting time that passes midnight.

I tried several formulas and converted it into VBA code, but I failed. Here's the formula that I recently converted to code:

=COUNTIFS(B$2:B$7,"<"&F9,C$2:C$7,">"&E9)

and

=SUMPRODUCT(--(B2:B7<F2),--(C2:C7>F1))

Any ideas?

Upvotes: 0

Views: 1509

Answers (1)

Thelnternet
Thelnternet

Reputation: 174

My solution utilizes two formulas.

Your current problem is to tell if a worker who works (for example) between 10:00pm - 3:00am. In order to reduce the risk of confusing the system, I opted to use a 24 hour clock. To minimize the need for input such as a date in addition to the time, I created a table as such: Table example

Each cell contains the formula =IF($H$3>C3,IF(IF(C3>D3,D3+24,D3)>$H$3,"X",""),""). The cells under "Active?" displays an "X" as long as the worker is currently on the clock.
Active formula example

The cell to check how many employees are on the clock, it runs a simple count if formula =COUNTIF(E3:E6,"X") to count how many instances of "x" in the Active column are present. Count formula example

To operate, you simply extend the range for any additional workers and fill out the cells accordingly. Once the table is populated, you can verify who is working by typing in the desired time in H3. Hope this helps!

Upvotes: 0

Related Questions