+ Reply to Thread
Results 1 to 11 of 11

Formula to calculate if employee is working on each half hour

  1. #1
    Registered User
    Join Date
    07-15-2012
    Location
    Bury St Edmunds, England
    MS-Off Ver
    Office 2010
    Posts
    9

    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
    Last edited by holowugz; 07-18-2012 at 04:58 PM. Reason: Solved

  2. #2
    Registered User
    Join Date
    05-13-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    17

    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
    Attached Files Attached Files

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    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.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    07-15-2012
    Location
    Bury St Edmunds, England
    MS-Off Ver
    Office 2010
    Posts
    9

    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. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    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. #6
    Registered User
    Join Date
    07-15-2012
    Location
    Bury St Edmunds, England
    MS-Off Ver
    Office 2010
    Posts
    9

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

    Quote Originally Posted by MarvinP View Post
    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. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    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. #8
    Registered User
    Join Date
    05-13-2008
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    17

    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
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-15-2012
    Location
    Bury St Edmunds, England
    MS-Off Ver
    Office 2010
    Posts
    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. #10
    Registered User
    Join Date
    07-15-2012
    Location
    Bury St Edmunds, England
    MS-Off Ver
    Office 2010
    Posts
    9

    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

    *Uploader would not let me upload*

    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. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1