Microsoft Excel - show staff numbers in work using vba and time calculator

Asked By Paul Potter on 10-Jun-10 04:10 AM
Hi, I am looking for some advice please?

Column A runs 08:00 to 18:00 (in 1/2 hours), and row 1 runs Monday to Friday.  I am trying to code a userform so that I can enter the shift of one of my staff (120 in total), and if they start at 09:00 to 17:00 on a Monday, they will receive a 1 for every 1/2 hour that they would be at work (until 17:00), their entry would add onto any of the other entries for the same 1/2 hour periods for that day, and so on. 

I have 47 different shift patterns, and am trying to get a hold on how many staff we have in at any time. 

Can anyone help please?

Thanks, Paul 
Parag Satpute replied to Paul Potter on 10-Jun-10 10:28 AM
I am not able understand your statement "Column A runs 08:00 to 18:00 (in 1/2 hours), and row 1 runs Monday to Friday."

If Column A has 08:00 to 18:00 vertically then how can Monday to Friday start from ROW 1
OR
If Column A has 08:00 to 18:00 horizontally then how can Monday to Friday start from ROW 1

Please if possible attach a sample file or elaborate more specifically so that i can help you.
Paul Potter replied to Paul Potter on 10-Jun-10 10:43 AM
Hi there, cell A1 is blank.  Cells B1 > F1 are Monday to Friday.  Cell A2 has 08:00, A3 has 08:30, A4 is 09:00 and so on.  Sorry if I didnt make myself clear.

Thanks, Paul

Could you upload a sample please? - Scott Gall replied to Paul Potter on 10-Jun-10 11:14 AM

Upload sample pls

sample attached - Paul Potter replied to Scott Gall on 10-Jun-10 11:31 AM

Hi there, hope this helps.  There are details of some of the shift patterns on there too!  

Paul Potter replied to Paul Potter on 10-Jun-10 11:33 AM
Rolf Jaeger replied to Paul Potter on 10-Jun-10 06:11 PM
Hi Paul:

I am afraid you have to specify your requirements a LOT more clearly before anyone here can provide you with any specific suggestions. You might have to give a specific example and explain the rows in your 'Full Time - Rostered' table.

Best wishes,
Rolf
Paul Potter replied to Rolf Jaeger on 11-Jun-10 07:47 AM
Hi there.  Column H is a sample of all of the shift patterns my staff currently do.  I am building a userform so that I can count how many of my staff are in at any one time.  The goal is that the userform will have a combobox for monday>friday, and a start time combobox and an end time combobox.  I can then select which day I am referring to, select a start time and an end time and click ok.  The columns b,c,d,e and f will be populated with a 1 for each corresponding 15mins that that staff member will be in for that day of the week.  The "1" will be added to any other entries in the same cell, until I can build a picture of the times of day when I have 10 staff in (08:00) and 120 staff (between 10:00 and 14:00).  I can do this manually, but automating this should save some time.  Hope this clears this up.
Rolf Jaeger replied to Paul Potter on 16-Jun-10 02:26 AM
Hi Paul:

sorry about the delayed response and for being so dense, but I am remain unclear on what you are trying to do. Let me ask you a few specific questions to see whether that might clear up where I am in the dark.

- do you have one worksheet for each of your employees?

- how do the 1's get added? and by whom?

- what do you mean by 'The "1" will be added to any other entries in the same cell...'?

Looking forward to hearin back,
Rolf
Paul Potter replied to Rolf Jaeger on 16-Jun-10 03:42 PM
Hi Rolf.  Thanks for coming back to me.  I have completed this process manually now, but would still like to achieve an automated method.

Basically, I have a list of 47 different shift patterns for my 120 staff.  I am looking at when the most staff are at work, and when I am quietest.  I have designed a userform with 3 comboboxes.  1 for day of week.  1 for start time, and 1 for end time.  I am looking for when I unload the userform, a "1" is added to the corresponding cells.  So if I select, Monday 08:00 start and 12:00 finish, and click ok, I would like the userform to enter a "1" into each 15 minute segment between 08:00 and 12:00.  As I go through each staff members shifts, going through this process, I am expecting more than just one staff member to be in between 08:00 and 12:00, so the userform unloading would add the "1" for that staff member to the total already in the cell, whether it's 10 or 30 - depending upon how many times I have unloaded the userform with comboboxes covering that timespan.

The manual process I have done, was simply to have all of the staff names and shifts along row a (b1 and right) and 08:00 (monday) to 18:00 (friday) down column a in 15 minute chunks, and just entered the "1"'s for each staff members shifts, but this was laborious.  And then at the very right hand side, just an =sum(a3:ac3) for example, and dragged it all of the way down.

Does this make sense?  Thanks, paul
Try this alternative - Rolf Jaeger replied to Paul Potter on 18-Jun-10 12:31 PM

Hi Paul:

I think I now understand what you are trying to do. HOWEVER it appears  to me that the approach you have decided on is still rather time-consuming (and probably error prone), AND is not utilizing Excel's capabilities as much as you could. I therefore wuold like to propose an alternative approach.

I have created a workbook (one for Excel 2003 and one for Excel 2007) with the following worksheets:

- PleaseRead
- Aggregate (this is the aggregate of all staff shifts)
- Staff (a list of your staff member and their shift patter (1 through 47))
- Pattern1
- Pattern2
...
- Pattern47

Once you populated the pattern worksheets you can simply assign (by number) any of the patterns to any of your staff members (in the 'Staff'f worksheet). To obtain the aggregate shifts for these assignments you simply go to the 'Aggregate' worksheet and click on the blue ractangl labeled 'Aggregate Shifts' and you get the result I think you are hoping for.

Hope this helped. If is does I would appreciate it if you were to let me know and read the worksheet labeled 'PleaseRead'.

Best wishes,
Rolf

Paul Potter replied to Rolf Jaeger on 18-Jun-10 06:12 PM

Hi Rolf, from your description I believe you have nailed it.  The links are bringing up "page not found", I would love to have a look at the document, in 2003.  Could you email it to me at eggheadcafe@maverikk.co.uk please?


Thanks for all your time and effort with this.  Paul