Help with rostering information

[Doozer1979]

Hello All,

I have to analyse a lot of rostering information.

The information comes from a data warehouse in text format and describes what shifts staff are on. The data comes out of the data warehouse in the format hh:mm - hh:mm (ie; 15:00 - 22:00, or 07:00 - 15:00). The shift patterns vary quite a lot.

What i need to do is go through this list and see how many staff i have on in each hour of the day. My thoughts for doing this were to to have another sheet with 24 cells that correspond to each hour of the day, and then have a function that loops through the rostering information and increments the cells by 1 if the shift includes that hour in its range.

Trouble is i have no idea how to do this in excel or vba, or even if i'm going down the right path.

Can anyone offer any advice?

 

[DonkeyOte]

Doozer, assuming name of staff member in A2, time str in B2 and say hours 1-24 in C1:Z1 and that strings are constant the formula at bottom would work for most entries, however, most likely it would need tweaking pending answers to the following:

1 - do people work across midnight (ie 22:00 - 05:00) -- if they do how do you determine which time is active for the day -- 10pm - midnight or 00:01 to 5 am?

2 - do people always start on the hour ? if not do you want to adjust 0/1 according to % of hour work ? ie 10:30 - 17:00 -- for hour 10 = 0.5 rather than 1)

below is C2 formula -- can be copied across and down for all hours / staff to create matrix -- you can then sum etc to get total staff in given hour.

=IF(AND(0+LEFT($B2,2)<=C$1,0+MID($B2,9,2)>=C$1),1,0)

 

[Doozer1979]

Thanks an excellent solution that was far briefer than the one i'd planned!

1. Shifts do span across days, but i don't need to know total hours per day or anything. I simply want to know how many people are on in each hour of the day. I hope that answers your question? The formula given doesn't work at all if i put in 23:00 - 07:00 as the shift.

2. shifts start hh:15, hh:30, hh:45, hh:00. I'm assuming that we could just extend the C1:Z1 range to insert the increments to take into account of this and then use VALUE to convert 00:15 to <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num="1.0416666666666666E-2">0.010417</TD></TR></TBODY></TABLE> (for example) and the work it out from that? Or am i overcomplicating matters?!


Once again thanks very much for your help

 

[DonkeyOte]

Your example of:

23:00 - 07:00

Key thing here is should the formula assume it's interested only in 23 to 24 or 00 to 07 ??
ie which day in the span is the one being calculated in the matrix ???

 

[Doozer1979]

Your example of:

23:00 - 07:00

Key thing here is should the formula assume it's interested only in 23 to 24 or 00 to 07 ??
ie which day in the span is the one being calculated in the matrix ???

I've sorted the problem with the increments by using

=IF(AND(VALUE(0+LEFT($B2,5))<=C$1,(VALUE(0+MID($B2,9,5)))>=C$1),1,0)

With regards to shifts spanning over days, i can probably get away with not doing it for overnight shifts as i'm only concerned with peak shifts which for me is 06:00 - 22:00.

However if i was to need this overnight shift patterns always finish at 1am so i'd be more concerned with anything prior to midnight.

Thanks

 

[DonkeyOte]

Using your prior formula -- expanding to account for "rollover" shifts...

IF(AND(0+LEFT($B2,5)<=C$1,OR(0+MID($B2,9,5)>=C$1,(0+MID($B2,9,2)<0+LEFT($B2,2)))),1,0)

This still doesn't calculate % of 1 though for mid hour starts which is not ideal as it will skew your subsequent calculations if based on headcount denominator.

 

[DonkeyOte]

Doozer,

given your note that you're not really looking at people whose shift started in the "prior" day (ie 23:00 yesterday to 07:00 today) I think you could use the following to give you a really accurate headcount based on minute start/end

Again, using same layout - so this would be C2 formula (to be copied across matrix)

<b>EDIT: can not display formula -- presumably Mr E interpreting some tags incorrectly... PM me if you want it.</b>

With hours 1 to 23 in C1:Y1

If you decided you were interested in hour 0 on start day you could add another argument to the choose.

I'm sure this can be done better by one of the others here but thought I'd post up my effort nonetheless in case it helps

 

[Doozer1979]

Cheers,


Thanks a lot for your help