Employee Availability Problem

Bombaygin

New Member
Joined
Nov 1, 2013
Messages
35
Hello,

I am hoping for some help. I have to create a table that shows how many employees are available to work during each hour of the day for each day of the week. My problem is I have no idea what approach to take or formulas to usez to get that information from my export of data with 5000+ rows on the availability of 900+ employees. Availability times are exported like below with some employees having three windows of availability on the same day in the same cell. I would like to find a way to display this availability like the below table. It would then be used to graph against customer demand per hour. Any suggestion would greatly be appreciated.

EmployeeMondayTuesdayWednesday
Steve8:00-13:00;18:00-23:0013:00-23:008:00-12:00;13:00-16:00;18:00-22:00
Fred10:00-18:0010:00-18:0011:00-19:00
Mike7:00-15:007:00-15:00; 17:00:19:00N/A

<tbody>
</tbody>

TimeMondayTuesdayWednesday
7:0011
8:0021
1
9:00211
10:00321
11:00322

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Test in a copy of your workbook.

Assumptions (advise if any are incorrect or cannot be arranged)
- A Summary sheet for the results exists and has column A and row 1 similar to the first screen shot below.
- Any other data that may already be on the Summary sheet (apart from column A & row 1) can be deleted.
- The string in your sample for Mike on Tuesday ("17:00:19:00") is a typo and should have been "17:00-19:00"
- All availability blocks on the 'Data' sheet start and end on a whole hour (xx:00) like all your samples.

Excel Workbook
ABCDEF
1TimeMonTueWedThuFri
20:00
31:00
42:00
53:00
64:00
75:00
86:00
97:00
108:00
119:00
1210:00
1311:00
1412:00
1513:00
1614:00
1715:00
1816:00
1917:00
2018:00
2119:00
2220:00
2321:00
2422:00
2523:00
Summary



Rich (BB code):
Sub Availability()
  Dim a, b, Blocks, StartStop
  Dim r As Long, c As Long, i As Long, j As Long, ub2 As Long
  
  a = Sheets("Data").Range("A1").CurrentRegion.Value    '<-Check sheet name
  ub2 = UBound(a, 2)
  ReDim b(1 To 24, 1 To ub2 - 1)
  For r = 2 To UBound(a)
    For c = 2 To ub2
      Blocks = Split(Replace(a(r, c), " ", ""), ";")
        For i = 0 To UBound(Blocks)
          StartStop = Split(Blocks(i), "-")
          If UBound(StartStop) > 0 Then
            For j = Val(StartStop(0)) To Val(StartStop(1)) - 1
              b(j + 1, c - 1) = b(j + 1, c - 1) + 1
            Next j
          End If
        Next i
    Next c
  Next r
  With Sheets("Summary").UsedRange.Offset(1, 1)         '<-Check sheet name
    .ClearContents
    .Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End With
End Sub

Sample Data:

Excel Workbook
ABCD
1EmployeeMondayTuesdayWednesday
2Steve8:00-13:00;18:00-23:0013:00-23:008:00-12:00;13:00-16:00;18:00-22:00
3Fred10:00-18:0010:00-18:0011:00-19:00
4Mike7:00-15:007:00-15:00; 17:00-19:00N/A
Data



Summary sheet after code:

Excel Workbook
ABCD
1TimeMonTueWed
20:00
31:00
42:00
53:00
64:00
75:00
86:00
97:0011
108:00211
119:00211
1210:00321
1311:00322
1412:00321
1513:00232
1614:00232
1715:00122
1816:00121
1917:00131
2018:00122
2119:00111
2220:00111
2321:00111
2422:0011
2523:00
Summary
 

Bombaygin

New Member
Joined
Nov 1, 2013
Messages
35
Hi Peter, I greatly appreciate your help. I have next to no experience with VBA and did not give thought to it as a solution for my problem. So I gave a dumb-down example of my export data. Your macro works perfect with this, but not with my actual export data. Unfortunately, with next to zero knowledge of VBA I am unable to adapt your work. Are you able to still help? A snap shot of the actual export is below.
Thanks
Availability%20Export_zps2elz4aog.jpg
[/URL][/IMG]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
1. Unfortunately, a picture is not a lot of use as I cannot copy the data from a picture to my worksheet to test - & I'm not intending to manually type that much out. ;)
My signature block below has suggestions for how to post small screen shots directly in your post as I did above. Then the data can be copied to a worksheet to test.
You don't need to post the whole worksheet, a section similar to the image you provided should probably do, though a description about how extensive the sheet actually is would be useful. Make sure you disguise any sensitive or personal information before posting it.

2. You didn't confirm anything about a Summary sheet. Does it exist, what does it look like?

3. You haven't said what to do in the summary if an employee is only available for half an hour (or possibly only 5 minutes) of a one-hour block, like in cell E17 of your image. Does the summary actually consist of one hour blocks or something else?

Remember, we are not familiar with your workbook so have to rely on what you tell and show us about it. The better we understand what you have and what you need, the more likely you are to get a good result and the less likely we are to be developing suggestions that turn out to be no use. :)
 

Bombaygin

New Member
Joined
Nov 1, 2013
Messages
35

ADVERTISEMENT

Hi Peter,

Thanks for clarifying what you need. My summary sheet is the same as yours consisting of one hour blocks. An availability for half an hour (or possibly only 5 minutes) of a one-hour block would be treated as a full hour. The export sheet would have up to 3000 rows of data with columns A to I.

Here is the screen shot as you requested.


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Employee Roster Report</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Period Capacity for all</td><td style=";">Total Capacity per Day and Period:</td><td style="text-align: center;;">Monday</td><td style="text-align: center;;">Tuesday</td><td style="text-align: center;;">Wednesday</td><td style="text-align: center;;">Thursday</td><td style="text-align: center;;">Friday</td><td style="text-align: center;;">Saturday</td><td style="text-align: center;;">Sunday</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">employees in range: </td><td style=";">23195.75 hours can be booked.</td><td style="text-align: right;;">3155.62</td><td style="text-align: right;;">3030.81</td><td style="text-align: right;;">3202.24</td><td style="text-align: right;;">3429.8</td><td style="text-align: right;;">3484.36</td><td style="text-align: right;;">3526.18</td><td style="text-align: right;;">3366.74</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Department: Home Support</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Emp.Category: R35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Roster Item:</td><td style=";">Period Totals:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Availability</td><td style="text-align: right;;">70</td><td style=";">8:00-13:00;13:00-23:00</td><td style=";">8:00-13:00;13:00-23:00</td><td style=";">13:00-23:00</td><td style=";">13:00-23:00</td><td style=";">17:00-22:00</td><td style=";">N/A</td><td style=";">8:00-13:00;13:00-23:00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Department: Home Support</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Emp.Category: Casual CHW/LPN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Roster Item:</td><td style=";">Period Totals:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Availability</td><td style="text-align: right;;">49</td><td style=";">7:00-15:00</td><td style=";">7:00-15:00</td><td style=";">7:00-19:30</td><td style=";">7:00-15:00</td><td style=";">N/A;Vacation</td><td style=";">N/A;Vacation</td><td style=";">N/A;Vacation</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Department: Home Support</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Emp.Category: Casual CHW/LPN</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Roster Item:</td><td style=";">Period Totals:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Availability</td><td style="text-align: right;;">28</td><td style=";">7:00-15:00</td><td style=";">N/A</td><td style=";">7:00-12:00</td><td style=";">N/A</td><td style=";">N/A</td><td style=";">7:00-12:00</td><td style=";">7:00-12:00;17:00-22:00</td></tr></tbody></table><p style="width:14.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">EmployeeRosterReport (1)</p><br /><br />
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Thanks for providing some copyable data & further information about the scale of your data.

I do still have a concern regarding row 4 of this data that I would like more information about. The image in post #3 contains dates in this row. The screen shot in post #4 contains day names.
In addition, the post #3 image had 27 Feb 2016 in column C - a Saturday - whereas post #4 starts with Monday in column C.


Do I need have those issues clarified, or is it just that anything in column C of the data sheet goes into column B of the summary, column D of data to column C of summary & so on for 7 columns?
 

Bombaygin

New Member
Joined
Nov 1, 2013
Messages
35
The day of the week starting in column C can be different depending on what date range you choose to run. So the latter of your question is correct; anything in column C of the data sheet goes into column B of the summary, column D of data to column C of summary & so on for 7 columns.

Thanks
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
So the latter of your question is correct; anything in column C of the data sheet goes into column B of the summary, column D of data to column C of summary & so on for 7 columns.
Give this one a try. Remember to check the sheet names.

Rich (BB code):
Sub Availability_v2()
  Dim a, b(1 To 24, 1 To 7), vRws, vCols, Blocks, StartStop
  Dim r As Long, c As Long, i As Long, j As Long
  
  Application.ScreenUpdating = False
  Sheets("Data").Activate                               '<-Check sheet name
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    vRws = Application.Transpose(Filter(Evaluate(Replace("transpose(if(#=""availability"",row(#),""|""))", "#", .Address)), "|", False))
  End With
  vCols = Evaluate("transpose(row(3:9))")
  a = Application.Index(Cells, vRws, vCols)
  For r = 1 To UBound(a)
    For c = 1 To 7
      Blocks = Split(a(r, c), ";")
      For i = 0 To UBound(Blocks)
        StartStop = Split(Replace(Blocks(i), "-", ":"), ":")
        If UBound(StartStop) > 2 Then
          If StartStop(3) <> "00" Then StartStop(2) = StartStop(2) + 1
          For j = Val(StartStop(0)) To Val(StartStop(2)) - 1
            b(j + 1, c) = b(j + 1, c) + 1
          Next j
        End If
      Next i
    Next c
  Next r
  With Sheets("Summary").Range("B2:H25")         '<-Check sheet name
    .Value = b
    .Parent.Activate
  End With
  Application.ScreenUpdating = True
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,575
Messages
5,596,961
Members
414,114
Latest member
Lost_User21

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
Top