+ Reply to Thread
Results 1 to 8 of 8

Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

  1. #1
    Registered User
    Join Date
    03-31-2013
    Location
    Glasgow, Scotland, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Hello everyone and anyone that can help.

    I'm looking for some help to calculate holiday hours using an "H", or meeting hours using an "M", in a time cell instead of an hour to state that it is a holiday day, and distinguishing these hours from standard working hours to create two different totals.

    I currently have this:

    =SUM(G27-F27)*24-IF((G27-F27)*24>6,0.5,0)

    Stating - Clock Out time minus Clock In time, multiply by 24, then, if the value is greater than six hours, remove a half hour break

    I want to add:

    -If G27 and G26 have an 'H' (or an "M") value then take the value of a related cell in sheet123, cell123, (or sheet321, cell321 for M/Meeting) for their daily holiday hours, if these cells have a time then use above formula to work out working hours.

    Also, is there a way to distinguish between holiday/meeting hours and working hours, so that I am able to automatically calculate these two totaling cells:

    Trading Hours (working hours)
    [SUM(H42,L42,P42,T42,X42,AB42,AF42) - easy SUM function for all total hours, but if a member of staff is on holiday I need this function to ignore their hours and add them below instead]

    Non-Trading Hours (holiday / meeting hours)
    [Reasonably the same formula, but in reverse - only counting H (holiday) or M (meeting) hours, and discounting all working hours]

    I presume that one way of doing this is to have an H in the cell on sheet123. So, if someone is on holiday, their daily hours will be 6.00 H for example. Then use a formula to calculate total hours, which includes an H if already apparent in a cell.

    However, I'm not sure how to do this, and after that, I'm even less sure of how to distinguish and calculate separately trading and holiday hours.

    I know this is a lot, but if anyone can help, I would be much obliged.

    Thanks in advance!

    S.

    ----

    Other forums this question is on:

    http://www.mrexcel.com/forum/excel-q...cheduling.html

    http://www.ozgrid.com/forum/showthread.php?t=176960
    Last edited by myactiondesign; 03-31-2013 at 03:11 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Any chance of giving us some sample data to make it easier?
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    03-31-2013
    Location
    Glasgow, Scotland, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Of course.

    https://www.dropbox.com/s/ximiuz11yl...20Progress.xls

    I don't know how to attach files, so here is a link to the file shared on dropbox.

    This is the rota I am working on so far. As it stands, it works fine, but needs a lot of manual editing for holidays and meetings.

    You can see that putting an H in Ryan R's day gives a value error (of course), and underneath with Laura I attempted to write formulae for what I wanted to do with limited knowledge, and trying to literally spell out what I want it to do as I would speak it. It didn't work.

    So for example, all the codes work to get total hours, excluding breaks etc, including NI Tax and so on. However, if, in Laura's case, I put an H to signify a holiday, I want it to count towards the staff's total hours, but then separate it at the bottom right, between total working (Trading) and total holiday (Non-Trading) hours.

    Any ideas?

    PS. Sheet 2 has National Insurance calculations, Sheet 1 will be, but is only draft at the moment, holiday day/hours contracts.
    Last edited by myactiondesign; 03-31-2013 at 02:06 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Just looking through the file... Be back in a while

  5. #5
    Registered User
    Join Date
    03-31-2013
    Location
    Glasgow, Scotland, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Thanks Andy!

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Put this in H28... This eliminates the error messages by only calculating the hours if both in and out times are present.

    =IF(AND(ISNUMBER(F28),ISNUMBER(G28)),(G28-F28)*24-IF((G28-F28)*24>4,0.5,0),0)

    You can then copy/paste the formula down through the total hours calculation columns and it eliminates the errors.
    Last edited by AndyLitch; 03-31-2013 at 02:20 PM.

  7. #7
    Registered User
    Join Date
    03-31-2013
    Location
    Glasgow, Scotland, UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Thanks Andy!

    I've added a little. It now reads:

    =IF(AND(ISNUMBER(F28),ISNUMBER(G28)),(G28-F28)*24-IF((G28-F28)*24>4,0.5,0),Sheet1!C14)

    I had a bit of value error trouble with trying to put 5H in sheet1 (meaning five hours holiday).

    What I've done to make things easier, is made sheet1 count whole or half day holidays.

    So, if it is a full holiday, the value is 1, if half, the value is 0.5.

    Now what I do is state in the totaling formula for Trading Hours that if a cell has a value of less than five, ignore it (holidays excluded).

    OK, OK. So I still have to check contract holiday hours, manually enter them, but I'm getting there.

    Thanks for your help Andy.

    If you have any further ideas.

    S.

  8. #8
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Calculate Holiday / Meeting Hours Distinguished from Working Hours // Rota Scheduling

    Absolutely mate .. It's knocking off time for me tonight but I'll get back onto it tomorrow ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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