Formula to calculate if employee is working on each half hour

Hello,

I have a rota with all of my staff members in column A , and then on monday for example B would be the start time in 24h format and C the finish time.

Shift times can be literally anything I.e 13:20 - 19:45

On a separate sheet i need to have a formula to calculate how many employees would be on site on each half hour.

The matrix sheet will simply have times starting from 04:00 up until 02:00 going across in the columns
underneath i want the output of the formula for to state how many employees would be working on each half hour from the rota sheet.

can anyone help?

Kind Regards,

James

Hi James and welcome to the forum,

I have a little different formula that might be simpler. See the attached for my answer.

Hi holowugz,

To be a good forum user you should mark this problem as solved. To do this you would EDIT your original post and the click on Go Advanced. You then need to change the PREFIX to "Solved" and save the changes. This will let others know they don't need to read the problem and you are satisfied with it.

You can also click on the star below a post to thank the poster. This gives us reputation points, kind of like the Like button. I see that you already did this... Thanks for the rep.

Hi James,

I didn't expect the end time to be earlier than the start time. This would then imply you had different days of start and end.

Did you want to have an employee start at 10PM and end at 4AM the next day? I don't think this is what I imagined.

You will need to attach you workbook so we can see what you are seeing. To attach a workbook (better a sample with no personal info) click on "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area.

Hi James,

You have conflicting information. You have Start and end times for a single day. This is how I did the problem. I'd suggest you increase your hours to all 48 half hour slots. Then if a persons starts at 10 PM Monday and ends at 2 AM on Tuesday the next day you show them working on Monday 10Pm to 11:59PM and then fill in Tuesday from 0:01AM to 2 AM. This should then make your spreadsheet work.

I think the way you have it arranged with start and end times and Days BOTH makes the problem a lot harder or unsolvable.