# Help with rostering information

#### Doozer1979

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### DonkeyOte

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP

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

##### New Member

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

##### MrExcel MVP
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

##### MrExcel MVP
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

Last edited:

#### Doozer1979

##### New Member
Cheers,

Thanks a lot for your help

Replies
2
Views
113
Replies
11
Views
230
Replies
41
Views
522
Replies
1
Views
66
Replies
0
Views
203

1,191,589
Messages
5,987,518
Members
440,099
Latest member
wai2kit

### 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.

### Which adblocker are you using?

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

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