I am looking to create a dynamic schedule forecaster that will allow me to play with various models and move employees staffed days and hours around and have a table that updates automatically showing how many resources would be staffed at that given day/hour.
Using the attached doc as a sample, i am looking for how to formulate the formula for J3:P26 to count the amount of resources who will work any given hour on any given day, based on the info in column B, C and D. If i have to spell out each day in column B i am open to that as well (example, changing "Mon-Fri" to "Mon, Tue, Wed, Thur, Fri").
Thank you for the assistance Kaper! I see in your attachment that your formula works, but when i attempt to use it, it errors out and i get the #VALUE! response back. Even if i just select one of your cells and click on the formula and hit ENTER, it breaks the formula.
Im assuming its due to some default settings in my Excel, but cant figure out how to prevent that. I have a few employees to add to the list and im trying to expand the references to the data in B,C, and D rows 2-6, but i cant figure out how to prevent it from breaking the formula.
Also, if it makes it easier, i am open to changing Columns C & D to proper time if it helps shorten up the formula needed to calculate J2:P26.
I believe the issue here is that for the 2019 version of Excel the formula will need to be array entered.
1. Select a cell in Kaper's file
2. Press the F2 key to put that cell in edit mode
3. Press and hold the Ctrl and Shift keys while pressing the Enter key
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I am trying to document all the individual dates & times the various resources in my team work. Since we are 24x7, there are people working at any given hour on any given day. I need assistance in creating a formula to track how many resources would be on shift at any given hour on any given day.
I am completely open to changing the formatting of this data if it makes generating the formula any easier. At the end of the day, all i need to see is:
Employee Name
Days Staffed
Shift Start Time
Shift End Time
Updated table on the right counting all the resources working each hour each day (J3:P26)
Thank you everyone for your assistance, and please let me know if there are any questions!
This proposal changes the source data and converts it into an Excel table so that the formulas in columns J:P will update automatically as new data is added.
The changed data is highlighted amber.
The formula used to populate columns J:P borrows on the one Kaper provided:
In the case of overnight work, how is it calculated?
For example:
Employee U, from Wednesday to Sunday, from 9 PM to 6 AM, counting at 3 AM.
Is the calculation as follows?
From Wednesday 9 PM to Thursday 6 AM, count 1 at 3 AM on Thursday.
From Thursday 9 PM to Friday 6 AM, count 1 at 3 AM on Friday.
...
From Sunday 9 PM to Monday 6 AM, count 1 at 3 AM on Monday.
The total count for Employee E is 5.
Is it true? what is your expectation?
Option Explicit
Sub Staft()
Dim lr&, i&, j&, t&, p1&, p2&, rng, res(), cell As Range, hr As Date, wday$, wk$, wk2$, c As Boolean
Dim staH As Date, endH As Date
rng = Sheets("Data").Range("A3").CurrentRegion.Value
Sheets("Output").Activate
wk = "MonTueWedThuFriSatSunMonTueWedThuFriSat"
Range("B2:H25").ClearContents
For Each cell In Range("B2:H25")
hr = Cells(cell.Row, "A").Value
wday = Cells(1, cell.Column).Value
For i = 3 To UBound(rng)
p1 = InStr(1, wk, rng(i, 2))
p2 = InStr(p1, wk, rng(i, 3))
wk2 = Mid(wk, p1, p2 - p1 + 3)
c = False
If rng(i, 5) < rng(i, 4) Then
c = True
endH = TimeSerial(23, 0, 0)
Else
endH = rng(i, 5)
End If
If InStr(1, wk2, wday) Then
If hr >= rng(i, 4) And hr <= endH Then cell.Value = cell.Value + 1
End If
If c Then
wk2 = Mid(wk, p1 + 3, p2 - p1 + 3)
wday = Mid(wk, InStr(1, wk, wday) + 3, 3)
If InStr(1, wk2, wday) Then
If hr <= rng(i, 5) Then cell.Value = cell.Value + 1
End If
End If
Next
Next
End Sub
In the case of overnight work, how is it calculated?
For example:
Employee U, from Wednesday to Sunday, from 9 PM to 6 AM, counting at 3 AM.
Is the calculation as follows?
From Wednesday 9 PM to Thursday 6 AM, count 1 at 3 AM on Thursday.
From Thursday 9 PM to Friday 6 AM, count 1 at 3 AM on Friday.
...
From Sunday 9 PM to Monday 6 AM, count 1 at 3 AM on Monday.
The total count for Employee E is 5.
Is it true? what is your expectation?
i apologize for the delayed response, but i finally had time to validate the code. Everything looks like its working properly, with one exception.
The end of each shift is counting an extra hour than it should be. For example 8am - 5pm would only add a count in the 8am-4pm rows, as that resource would be getting off work at 5pm, and would not be present for the 5pm-6pm slot. Currently the code is adding a count for 8am, 9am, 10am, 11am, 12pm, 1pm, 2pm 3pm, 4pm, and 5pm, where it should add its last tick in the 4pm row as that would signify "4pm-5pm".
I dont see an easy place to edit the code built as i dont see where the start/end times of shifts are referenced to try and add a "-1" to the shift end.
I imagine that you are responding to bebo, however I will put this here anyway.
In order to accomplish the request in post #11, the array entered formula** from post #6 may be modified to read:
LinkBack URL
About LinkBacks
Hourly Headcount Calculator
Register To Reply
Originally Posted by bebo021999
Bookmarks