# Formula to calculate if employee is working on each half hour

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

2. ## Re: Formula to calculate if employee is working on each half hour

Hi Holo,

Attached is a cheats solution to your problem. Sheet 2 has each employee listed and then a sum at the bottom. Someone far brighter than I will come up with a more elegant solution, but this might tide you over.

Tim

3. ## Re: Formula to calculate if employee is working on each half hour

Hi James and welcome to the forum,

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

4. ## Re: Formula to calculate if employee is working on each half hour

I am amazed at the quick response thank you both very much, problem solved

5. ## Re: Formula to calculate if employee is working on each half hour

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.

6. ## Re: Formula to calculate if employee is working on each half hour

Originally Posted by MarvinP
Hi James and welcome to the forum,

I have a little different formula that might be simpler. See the attached for my answer.
Slight issue, when ever i enter a rota time that goes to from PM to AM I.E 15:30 - 01:30 , the formula stops working, any advice at all?

James

7. ## Re: Formula to calculate if employee is working on each half hour

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.

8. ## Re: Formula to calculate if employee is working on each half hour

Hi James,

That was a dilly of a pickle. I've made an adjustment to my original which seems to be working. It means adding a "Duration" column to you first sheet, but hopefully that's workable for you.

Here you go

9. ## Re: Formula to calculate if employee is working on each half hour

Hello,

Sorry my fault the latest shift would be at approx 02:00,

the workbook you gave me earlier is attached with the changes made showing what happened with the data:

WorkHrs from Start and End Rota2 (1).xlsx

Tried attaching my main one but it is at 8mb atm and will not upload.

Kind Regards,

James

10. ## Re: Formula to calculate if employee is working on each half hour

Was able to reduce my document size:

Rota attached:

http://www.qfpost.com/file/d?g=WZ3akPKQC

The bar associate checker sheet is where i am reviewing the staffing levels only monday is "active" as i stopped working when i cam into the midnight issue.

James

11. ## Re: Formula to calculate if employee is working on each half hour

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)