+ Reply to Thread
Results 1 to 152 of 152

Allocating hours to correct deparments using formulas

  1. #1
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Allocating hours to correct deparments using formulas

    Hi all,

    I have a spreadsheet which contains names and shifts they work on - It also includes the week days.

    The hours a person works are inputted on a daily basis and then the correct department is selected through a dropdown box to the right of the hours.

    My objective is to make the sheet use a 'universal' formula which can be dragged across the sheet to make it all work by calculating the hours correctly across all departments. It also needs to take in consideration if that person has worked here for longer than 12 weeks which is calculated in column 'C'.

    Columns from 'BR' onwards just work out the cost based on the hours inputted.

    I assume this will be a lengthy IF statement but if someone can assess the document and see an easier way - that would be great!


    Let me know if anyone can help!?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello and welcome to the forum.

    I fear you are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information with ad hoc reports. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality.

    So before you get too far with this I'd create a single sheet database that contains the following columns

    Record Date
    Name - *
    Start Date - could be from a lookup table
    Shift *
    Field name for whatever values in the drop downs in cols. F/H...etc. are called - *
    Hours

    The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    I understand what you're saying but the sheet must stay as it is as it's used by our HR team who like it this way.

    Can you assist with a 'universal' code which can be dragged across to make all work?

    Many thanks,
    Luke

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    This will work with the PROVISO that all the drop down selections are made

    =SUMIF($F8:$R8,X6,$E8:$Q8)


    BUT ...

    On your sample the PO sums to 41 where I expect it to be a maximum of 40.

    So how is this handled ....

    we could use =MIN(SUMIF($F8:$R8,X6,$E8:$Q8),40)

    but this could not be dragged across

    In BR8 etc the SUM is not required ..

    =IFERROR(BR$7*X8,"-")

    will suffice.

    You will need a formula per column (some of which may be the same format) which can be dragged down

  5. #5
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by JohnTopley View Post
    This will work with the PROVISO that all the drop down selections are made

    =SUMIF($F8:$R8,X6,$E8:$Q8)


    BUT ...

    On your sample the PO sums to 41 where I expect it to be a maximum of 40.

    So how is this handled ....

    we could use =MIN(SUMIF($F8:$R8,X6,$E8:$Q8),40)

    but this could not be dragged across

    In BR8 etc the SUM is not required ..

    =IFERROR(BR$7*X8,"-")

    will suffice.

    Hi there,
    How can this be adapted to be dragged across?

    Is it possible?


    Thanks

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    As my amendment to the previous post: you will need a formula per column .

    a "universal" formula, if even possible, will be pretty complex.

    You will have to clearly define the logic associated with each column e.g. test for post 12 weeks

    I refer you to Richard's comment.

  7. #7
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by JohnTopley View Post
    As my amendment to the previous post: you will need a formula per column .

    a "universal" formula, if even possible, will be pretty complex.

    You will have to clearly define the logic associated with each column e.g. test for post 12 weeks

    I refer you to Richard's comment.
    This was my fear - I know it's possible but was aware of it's complexity.
    Was praying there was an easier way to formulate it whereby you guys could help.

    Thanks

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    .... so what now?

    If we knew ALL the details there may be a "simpler" way but no "universal" formula.

    Why is a single formula the imperative?

    How many of the columns could be addressed by my earlier formula (or something very similar) i.e. how many are simply calculated based on the drop-down value?

  9. #9
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by JohnTopley View Post
    .... so what now?

    If we knew ALL the details there may be a "simpler" way but no "universal" formula.

    Why is a single formula the imperative?

    How many of the columns could be addressed by my earlier formula (or something very similar) i.e. how many are simply calculated based on the drop-down value?
    Your formula works fine based on the drop down value.

    A single formula is simply easier to work with as if new headings are inputted, the formula can just be dragged across with no skills involved.

    I would have liked the formula to take into consideration column 'C' "Over 12 weeks" and the force the value into the correct column but I understand this is more difficult to do as the dropdown decides where it goes. Is this possible?

    Is there a way to automatically put the "Over 40 hrs" and "Over 40 hrs SUNDAY" in no matter what the dropdown value is?

    Many thanks

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    Can you mock up a file with some sample calculation (with/without OT), Post 12 weeks etc.?

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    I'll be out for a while but it may be picked up by someone else.

  11. #11
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi,

    I have attached what I would like to see and have added comments to the cells below FYI.

    Thanks a lot
    Attached Files Attached Files

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Hi Richard,

    I understand what you're saying but the sheet must stay as it is as it's used by our HR team who like it this way.

    Can you assist with a 'universal' code which can be dragged across to make all work?

    Many thanks,
    Luke
    When you say it's 'used' by HR do you mean they both read and write to it or just that they read the data?

    If the latter then I'd still urge you to capture the information in a normalised 2D table and output the data to the layout you currently have.

    If not then can I suggest a bit of HR re-education in this digitised world is needed

  13. #13
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188
    Quote Originally Posted by Richard Buttrey View Post
    When you say it's 'used' by HR do you mean they both read and write to it or just that they read the data?

    If the latter then I'd still urge you to capture the information in a normalised 2D table and output the data to the layout you currently have.

    If not then can I suggest a bit of HR re-education in this digitised world is needed
    Hi Richard, please can you send your proposed idea and what benfits that has?

    The layout at the minute can work but i just need help making it work. I just need the coding to make it work and then pivot tables will be used in the future. For now, my objective is to make this work.

    Many thanks

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    I laid out the bones of the database in my earlier post.

    The benefits are enormous since not only can you use the database to create the sort of adhoc and user defined reports you want, but perhaps more importantly you can analyse and summarise your data in ways you've not yet even thought about, all without touching a function or VBA.

    You'll create a new record for each unique entry row, in the 6 column field labels I indicated. The table will be longer of course but there will be no white spaces as you currently have.

  15. #15
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    I laid out the bones of the database in my earlier post.

    The benefits are enormous since not only can you use the database to create the sort of adhoc and user defined reports you want, but perhaps more importantly you can analyse and summarise your data in ways you've not yet even thought about, all without touching a function or VBA.

    You'll create a new record for each unique entry row, in the 6 column field labels I indicated. The table will be longer of course but there will be no white spaces as you currently have.
    Can I have help making the original attached document work please?
    That's all I ask - thanks

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    I have attempted to interpret your requirement on the one line sample: my observation is that the system is far too error-prone. No idea how "no" ("> 12 weeks" ) is to be handled

    in X7

    =IF(AND($E7>0,$F7=$X$5,$C7="Yes"),0,SUMIF($J7:$R7,$X$5,$I7:$Q7))

    in Y7

    =IF(AND($F7=$X$5,$C7="Yes"),SUMIF($J7:$R7,$X$5,$I7:$Q7),SUMIF($J7:$R7,$Y$5,$I7:$Q7))

    Other formulas are similar

    See results highlighted in yellow ( where I placed the formulae).
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by JohnTopley View Post
    I have attempted to interpret your requirement on the one line sample: my observation is that the system is far too error-prone. No idea how "no" ("> 12 weeks" ) is to be handled

    in X7

    =IF(AND($E7>0,$F7=$X$5,$C7="Yes"),0,SUMIF($J7:$R7,$X$5,$I7:$Q7))

    in Y7

    =IF(AND($F7=$X$5,$C7="Yes"),SUMIF($J7:$R7,$X$5,$I7:$Q7),SUMIF($J7:$R7,$Y$5,$I7:$Q7))

    Other formulas are similar

    See results highlighted in yellow ( where I placed the formulae).
    This works for the example on Person 1 - if however I change the dropdowns then yes there is a lot of errors possible.

    Would you be kind enough to draft up a version that will work 100% so I can see the difference in sheets and provide training if necessary?

    Many thanks John

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    .....if however I change the dropdowns then yes there is a lot of errors possible.
    Exactly: which why you need to re-think this (as per Richard's advice).

    I cannot draft a 100% version unless you specify in DETAIL all the conditions that apply. I (or any respondent) do not know your business and obviously have no idea of the criteria and their dependencies.


    In my view this requires a VBA application as the inter-dependencies are very complex and unlikely to be met using manageable formulae.

    And for such a solution, you do need a comprehensive specification.


    Consider using "Commercial Services".

  19. #19
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by JohnTopley View Post
    Exactly: which why you need to re-think this (as per Richard's advice).

    I cannot draft a 100% version unless you specify in DETAIL all the conditions that apply. I (or any respondent) do not know your business and obviously have no idea of the criteria and their dependencies.


    In my view this requires a VBA application as the inter-dependencies are very complex and unlikely to be met using manageable formulae.

    And for such a solution, you do need a comprehensive specification.


    Consider using "Commercial Services".

    The sheet needs to contain the info that is currently on there;

    -Name
    -Start Date
    -Over 12 weeks Y/N
    -Shift

    Then Saturday to Friday hours worked.
    Departments needs to be what is currently on the sheet.

    The conditions are that, if the person has worked longer than 12 weeks and the incorrect code from the dropdown has been used, it's automatically transferred into a (over 12 weeks heading) as there currently is on the sheet.
    The comments that I put on the second sheet that I posted explain why the numbers need to be where they are.


    It needs to be simple to use and the only output that is required is the total cost of the staff which is on the far right of the sheet.

    Is this something you can work with?

    Thanks

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Allocating hours to correct deparments using formulas

    One option, which may simplify things, is to have drop-downs for Saturday/Sunday which only applicable to those days and then similarly for Monday-Friday.

    When you have 45 categories (X to BP) then it is far from simple in that it relies TOTALLY on the input being correct.

    There is little point in having "criteria" [like OP,POP, .....] if we have to keep writing "error correction" formulae (which is what I had to do for the small example).

    As I suggested earlier you should consider using "Commercial Services" as this not a 5 minute piece of work.

  21. #21
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by JohnTopley View Post
    One option, which may simplify things, is to have drop-downs for Saturday/Sunday which only applicable to those days and then similarly for Monday-Friday.

    When you have 45 categories (X to BP) then it is far from simple in that it relies TOTALLY on the input being correct.

    There is little point in having "criteria" [like OP,POP, .....] if we have to keep writing "error correction" formulae (which is what I had to do for the small example).

    As I suggested earlier you should consider using "Commercial Services" as this not a 5 minute piece of work.
    How about we leave the error correction for now as my brief is to make this work and I was instructed that if the wrong data is inputted then the fault lies with the operator - which has just been confirmed again here.

    Would this make things much easier?

    If not, what commercial services would you suggest?

  22. #22
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    John was referring to the Commercial Services sub forum on this site.

    It seems such a shame though that you will need to jump through hoops just because you won't, or are not permitted by HR, to change the data entry layout as was suggested.

    I don't think I saw a response to the question about whether HR were just end users (i.e readers) of the data or if they input it.

  23. #23
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    John was referring to the Commercial Services sub forum on this site.

    It seems such a shame though that you will need to jump through hoops just because you won't, or are not permitted by HR, to change the data entry layout as was suggested.

    I don't think I saw a response to the question about whether HR were just end users (i.e readers) of the data or if they input it.
    I did however ask for a proposed draft (which I believe will be very similar).

    HR analyse the data, Team Leaders input it.

    Thanks

  24. #24
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    I did however ask for a proposed draft (which I believe will be very similar).

    HR analyse the data, Team Leaders input it.

    Thanks
    I read that as indicating you wanted a draft based on your original layout. Are you now saying that you will consider the database approach. i.e. the inputs will NOT be to your current sheet/layout but to a new sheet with a dedicated normalised 2 dimensional table that would be used to derive the reports you want.

    One other Q. You say team leaders (plural) input data. Are you able to confirm that only one team leader will use the file at any one time?

  25. #25
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    I read that as indicating you wanted a draft based on your original layout. Are you now saying that you will consider the database approach. i.e. the inputs will NOT be to your current sheet/layout but to a new sheet with a dedicated normalised 2 dimensional table that would be used to derive the reports you want.
    Yes, I had a feeling that the current layout would be too difficult to solve with simple formulae's hence coming on here to see if it was possible but it was confirmed that it's too difficult.

    Can I please see this 2D table with examples in there using our companies headings?

    Thanks

  26. #26
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    One other Q. You say team leaders (plural) input data. Are you able to confirm that only one team leader will use the file at any one time?
    Yes, 1 person at a time.

    Thanks

  27. #27
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi,

    Attached is the sort of thing I have in mind.

    A new daily record for each person will be added in B4:G4 of the Data tab. H4:O4 are working columns.
    Pressing the button 'Add Record to database' would copy the row 4 values to the database underneath.

    On the Variable Tab, G6:N22 is a matrix of Grades in G6:G22 and the Rate category in G6:N6. I've not completed this but you'll no doubt get the idea

    On the Reports Sheet selecting a w/c date in C3 and a name in C5 will populate the table underneath with the records from the database.

    A macro could then be written which would loop through all the names, add the name to C5 and then update an HR list with the totals for each person on individual rows.
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Attached is the sort of thing I have in mind.

    A new daily record for each person will be added in B4:G4 of the Data tab. H4:O4 are working columns.
    Pressing the button 'Add Record to database' would copy the row 4 values to the database underneath.

    On the Variable Tab, G6:N22 is a matrix of Grades in G6:G22 and the Rate category in G6:N6. I've not completed this but you'll no doubt get the idea

    On the Reports Sheet selecting a w/c date in C3 and a name in C5 will populate the table underneath with the records from the database.

    A macro could then be written which would loop through all the names, add the name to C5 and then update an HR list with the totals for each person on individual rows.
    Hi Richard,

    This would work however, HR need visibility of weekly cost by Area (Packing - Packing >12wks - Mixing etc.) and a total cost per week.
    Also, to add things to the mix, currently, on a new week, HR create a copy of the Week Commencing tab each week and a new one is filled in each week.
    Could this version handle this all on 1 sheet with reports to break down the costs by week and by area?

    Also, the "Add to database" button doesn't work my end?

    Many thanks,

  29. #29
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Yes of course the database can create all sorts of reports. That's why I mentioned ad hoc and user defined reports in my earlier post. That's because the data is in a format you can actually make use of. Any other data layout, (like your existing system) is a poor relation and could never offer any advantages over the flat 2 dimensional table.

    The workbook I sent was very much a first attempt to show you the sorts of things you can do. I never intended it to be a fully working delivered system. That is going to take more time and effort. The first thing I suggest you do is understand the standard techniques for extracting data into reports and Pivot Tables. e.g. the various filtering functionality, the SUMIFS() & COUNTIFS() functions and of course VBA macros in order to 'drive ' the system.

    I only showed the Add Record button to indicate how in practice you would add the record to the database. I didn't actually create the macro to do it.

  30. #30
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Yes of course the database can create all sorts of reports. That's why I mentioned ad hoc and user defined reports in my earlier post. That's because the data is in a format you can actually make use of. Any other data layout, (like your existing system) is a poor relation and could never offer any advantages over the flat 2 dimensional table.

    The workbook I sent was very much a first attempt to show you the sorts of things you can do. I never intended it to be a fully working delivered system. That is going to take more time and effort. The first thing I suggest you do is understand the standard techniques for extracting data into reports and Pivot Tables. e.g. the various filtering functionality, the SUMIFS() & COUNTIFS() functions and of course VBA macros in order to 'drive ' the system.

    I only showed the Add Record button to indicate how in practice you would add the record to the database. I didn't actually create the macro to do it.
    Hi Richard,

    Would it be possible for you to make this work?
    I can generate reports as long as the data entry works correctly.

    Many thanks

  31. #31
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    If by 'make this work' you mean add the new record to the database with a macro then yes.
    I'm going out shortly so won't be able to do it until later today.

  32. #32
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    If by 'make this work' you mean add the new record to the database with a macro then yes.
    I'm going out shortly so won't be able to do it until later today.
    Yes, to make the button work and to also add in the week commencing date to the record.

    Many thanks,

  33. #33
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi,

    See attached.

    It seems to me that the key to this system is:
    1. The matrix of Wage Rates for the different operations on the Variables Sheet. Please check this. I don't know your wage rate rules so it may be that not every cell in this table will be filled in. However it should contain a column for every type of rate variation.
    2. The three cells M5:N5 on the Data input sheet. I've interpreted what I understand your rules to be but these cells need to be tested. The object of course is that they should return the Column number in the matrix for whatever the precedent data inputs in B5:F5 dictate. The J5:L5 Wage cost formulae rely on finding the right column in the matrix.

    Going forward and when in production mode you may choose to hide Data column G and across since presumably the Team Leaders don't need to see these. The idea being to ensure the formulae are not overwritten accidentally. You could of course achieve the same thing by unlocking the 5 data entry cells and protecting the sheet. If you do that the macro to add the data will need tweaking so that it temporarily turns off sheet protection whilst the record is added.

    The final Search string field in the database may be a useful field for selecting particular records with Data Filter functionality. I generally add something like that even if it's not yet used. Experience tells me one day it will be useful.

    I've added a Pivot Table and some Slicer filters to give you an idea as to how this might work. You may find that with this you don't need to create a dedicated output summary of records for HR. The Pivot Table will automatically refresh every time you select the PT sheet
    Attached Files Attached Files
    Last edited by Richard Buttrey; 04-25-2017 at 03:04 PM.

  34. #34
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    See attached.

    It seems to me that the key to this system is:
    1. The matrix of Wage Rates for the different operations on the Variables Sheet. Please check this. I don't know your wage rate rules so it may be that not every cell in this table will be filled in. However it should contain a column for every type of rate variation.
    2. The three cells M5:N5 on the Data input sheet. I've interpreted what I understand your rules to be but these cells need to be tested. The object of course is that they should return the Column number in the matrix for whatever the precedent data inputs in B5:F5 dictate. The J5:L5 Wage cost formulae rely on finding the right column in the matrix.

    Going forward and when in production mode you may choose to hide Data column G and across since presumably the Team Leaders don't need to see these. The idea being to ensure the formulae are not overwritten accidentally. You could of course achieve the same thing by unlocking the 5 data entry cells and protecting the sheet. If you do that the macro to add the data will need tweaking so that it temporarily turns off sheet protection whilst the record is added.

    The final Search string field in the database may be a useful field for selecting particular records with Data Filter functionality. I generally add something like that even if it's not yet used. Experience tells me one day it will be useful.

    I've added a Pivot Table and some Slicer filters to give you an idea as to how this might work. You may find that with this you don't need to create a dedicated output summary of records for HR. The Pivot Table will automatically refresh every time you select the PT sheet

    This is absolutely brilliant - I've never seen slicer filters before!

    I will test this out today and let you know the results of it.

    But first, can the coding be adapted to allow worksheet protection as you mentioned previously?
    All of the documents we use here are protected for confidentiality and prevention of deleting codes!

    Many thanks!

  35. #35
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Also, How is night shift designated?

    I've tested it with Hygiene but there is no way to tell the system that they are a night worker.

    Can the Shifts be changed to:

    Red Shift
    Green Shift
    Night Shift

    Same for cleaning line, it's currently set at 'Cleaning Line Days' so the system would never know if they are working nights or not.

    Would you be able to have a look at that too please Richard?

    Many thanks

  36. #36
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi,

    As John alluded to earlier it's important at this stage to ensure that we have a detailed spec. Otherwise it's in the nature of the development of this sort of stuff that we play ping pong. Remember your data is meat and drink to you and dealing with it on a daily basis you naturally understand exactly what it represents and how it should work To us this is all new. We are not familiar with your terminology or business process.

    You ask how is night shift designated. Perhaps stating the obvious, but I have no idea. You need to clearly state a rule which uniquely and clearly defines what YOU mean by it.

    You say you've tested it with Hygiene. Again I have no idea what you mean by that and why Hygiene should be conceptually any different to any other work grade (or however you refer to that data type). You seem to imply that Hygiene is different in some way, maybe involving a night shift, but again I don't know your wage rules and can't comment and certainly can't code anything until I do understand.

    Ditto Cleaning Line days.

    The shifts are shown on the Variables tab. You just need to add or delete them as necessary. Don't add/delete rows on the Variables tab otherwise you'll create gaps in the other columns.

    So put together a detailed spec.. and importantly create example inputs for every permutation of data inputs (i.e. the basic stuff you enter on the Data tab) and manually add the results that the formulae should produce on the rest of the Data Tab row for every permutation.

    The idea behind this forum is that we help others to help themselves so you should be prepared to experiment and add stuff and try it out.
    Last edited by Richard Buttrey; 04-26-2017 at 05:49 AM.

  37. #37
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Same for cleaning line, it's currently set at 'Cleaning Line Days' so the system would never know if they are working nights or not.
    I assume this would mean just adding a "Cleaning Line NIGHTS" into the matrix?????

    And another thing is that overtime should only be applied if the operative has worked more than 40 hours. - this is highly important

    If an operative has worked more than 40 hrs for the week, only then the overtime rate is applied for the last day they worked and for the hours the worked overtime only for that 1 day.

    If they work 10 hours for 4 days, no overtime is applied, it's only applied when the work the 5th day (in this example) if they work 8 hours then 8 hours will be paid as overtime for the area they worked in.

    Sorry the spec is lacking detail - trying my best!

    Would you be able to have a look at that too please Richard?

    Many thanks

  38. #38
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    It seems to work fine if I add in "Packing Nights" "Hygiene Nights" etc with the hourly rates in place to the matrix as separate lines.

    It's just the overtime that is calculating wrong at the moment.

    If this can be sorted then I think it will work fine!

    Thanks

  39. #39
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    One way would be to add another Cleaning Line Nights to the matrix. However I see that the matrix already contains a Cleaning Line Days.
    Adding a new Cleanining Line Nights is not the best way of using the matrix since it is starting to build the concept of TIME into what's essentially just a labour GRADE and is compromising the design. The reference in the matrix should only be to Cleaning Line.

    The matrix should contain unique row labels, like Cleaning Line, and since the column labels already contain Post & Pre night columns (i.e. TIME) then the formula on O5 of the data sheet should be adapated. It may mean that a 6th Data entry field needs adding so that the user selects either Day or Night at data entry stage.

    Often for these wage calculation systems the user enters a start and end time (rather than in your case the number of hours) and the system can then work out both the number of hours, but also work out whether the times recorded put the record into a night or day shift.

    However we need a complete spec. which covers all aspects in order that these nuances can be understood. The temptation all the time is to compromise efficient design by adding bits and pieces (like the suggestion to add a Cleaning Line Nights 'grade') without thinking things through and putting everything on the table now.

    Assuming we are going to add a new 6th input column for Day/Night as a new Column G with everything shifted across one column, as I said, add rows and as many permutations of inputs to columns B:G that you can think of and then MANUALLY add what the results should be in the rest of the columns on the Data sheet. Then we can decide and work out what the formulae need to be to achieve the results you have worked out. As I said it's important to include all combinations. That will mean varying the

    1. day/night
    2. pre /post 12 weeks,
    3. Sunday/Weekday
    4. >40 and <40 hours -
    and any other variations you can think of.

    Since at the moment there are 4 basic input considerations each with a binary choice that implies 16 combinations. But if there are any exceptions or other unique rules that should apply then we need to know them now.
    Last edited by Richard Buttrey; 04-26-2017 at 06:47 AM.

  40. #40
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    One way would be to add another Cleaning Line Nights to the matrix. However I see that the matrix contains a Cleaning Line Days.
    Adding a new Cleanining Line Nights is not the best way of using the matrix since it is strating to build the concept of TIME into what;s essentially just a labour GRADE.
    The matrix should contain unique row labels, like Cleaning Days, and since the column labels already contain Post & Pre night columns (i.e. TIME) then the formulae on O5 of the data sheet should be adapated. It may mean that a 6th Data entry field needs adding so that the user selects either Day or Night at data entry stage.

    Often for these wage calculation systems the user enters a start and end time (rather than in your case the number of hours) and the system can then work out both the number of hours, but also work out whether the times recorded put the record into a night or day shift.

    We need a complete spec. which covers all aspects in order that these nuances can be understood. The temptation all the time is to compromise efficient design by adding bits and pieces (like the suggestion to add a Cleaning Line Nights 'grade') without thinking things through.

    Assuming we are going to add a new 6th input column for Day/Night as a new Column G with everytthing shifted across one column, as I said, add rows and as many permutations of inputs to columns B:Gdata that you can think of and then MANULLY add what the results should be in the rest of the columns. Then we can decide what the formulae need to be.
    Hi Richard,

    You say that adding a 'Nights' row into the matrix isn't good - I don't understand why not because it works?

    You have basically built what I've asked for but the only thing left is to handle to overtime issue.

    A typical operative will work 5 days @ 8 hrs/day.

    If he/she works 4 of those days @ 8 hrs and then 9 hrs on their last day, only 1 hour of overtime shall be paid based on their shift and area worked in.

    If this person works 4 days in the packing area and has worked longer than 12 weeks, they will be paid 32hrs at the "post 12 weeks" rate.
    If they work in mixing for their last day for 9 hours, 8 hrs will be paid at the "Post 12 weeks" mixing rate plus 1 hours mixing overtime.

    If however, an operative works 10 hrs a day but doesn't exceed 40 hours for the week, no overtime is paid.

    Does this make more sense?

  41. #41
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi,

    Please check the previous post I made since I edited it. Note the requirement to add the 16 combination examples.

    The reason it's not good practice to add another row to the matrix is for the same reason I reduced your original list (in column S) from 45 items to 16 in column J
    As I said in my edited post the matrix rows should deal with one concept i.e. Grade or Operation Type and the columns with the other concept of TIME.

    Whilst putting a 'Nights' row is theoretically possible it would compromise the design and complicate the formulae on the Data entry sheet. In short it's neither efficient nor elegant.

  42. #42
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188
    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Please check the previous post I made since I edited it. Note the requirement to add the 16 combination examples.

    The reason it's not good practice to add another row to the matrix is for the same reason I reduced your original list (in column S) from 45 items to 16 in column J
    As I said in my edited post the matrix rows should deal with one concept i.e. Grade or Operation Type and the columns with the other concept of TIME.

    Whilst putting a 'Nights' row is theoretically possible it would compromise the design and complicate the formulae on the Data entry sheet. In short it's neither efficient nor elegant.
    Hi richard, the 16 variables are the only ones that apply.

    Can you attach a new version that handles the overtime correctly based on my previous reply?

    The time concept is not required as the ops use a clock machine too and if needed, hrs can be obtained from there. This sheet just needs to have hours worked.

    Once the overtime works correctly and night shifts are added in im 100% it will all run fine.

    Many thanks

  43. #43
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Nevertheless, and as I asked and for the reasons I gave would you add some data which reflects an overtime record and ADD the results you expect. Be sure to combine this overtime with a Sunday and any other variable that is relevant.

  44. #44
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Nevertheless, and as I asked and for the reasons I gave would you add some data which reflects an overtime record and ADD the results you expect. Be sure to combine this overtime with a Sunday and any other variable that is relevant.
    Please see attached file with highlighted cells and comments.

    Many thanks
    Attached Files Attached Files

  45. #45
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Please see attached file with highlighted cells and comments.

    Many thanks
    Apologies, this is wrong as the following Sunday is a new week.

    See revised attached

    Thanks
    Attached Files Attached Files

  46. #46
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Apologies, this is wrong as the following Sunday is a new week.

    See revised attached

    Thanks
    The Record date field should have had a validation so that only valid dates are allowed. To be honest I thought it did but it seems to have got deleted.

    B11 is showing 37/4/2017 so I'm not sure whether your note in L11 means that B11 should be a Sunday e.g. 23/4/2017.
    L11 refers to using the Hygiene OT rate, but at the time of the entry which is for Mixing, how can the system work out that another Operation Type of Hygiene on another day is a modifying factor? What about any other Operation type?

    I'm also somewhat confused by your note in L15 since it refers to another (first entry) in the week.

    Are you saying that an entry on any one row is or could be subject to and modified by another entry on another day. If so this is a whole new dimension not previously discussed. The current system is assuming one entry per day per person with a single Operation Type.

    If someone can have two or more entries for different shifts on the same day or even on different days then a whole new rule or rules is required.

    Would you explain both these notes please.

  47. #47
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    The Record date field should have had a validation so that only valid dates are allowed. To be honest I thought it did but it seems to have got deleted.

    B11 is showing 37/4/2017 so I'm not sure whether your note in L11 means that B11 should be a Sunday e.g. 23/4/2017.
    L11 refers to using the Hygiene OT rate, but at the time of the entry which is for Mixing, how can the system work out that another Operation Type of Hygiene on another day is a modifying factor? What about any other Operation type?

    I'm also somewhat confused by your note in L15 since it refers to another (first entry) in the week.

    Are you saying that an entry on any one row is or could be subject to and modified by another entry on another day. If so this is a whole new dimension not previously discussed. The current system is assuming one entry per day per person with a single Operation Type.

    If someone can have two or more entries for different shifts on the same day or even on different days then a whole new rule or rules is required.

    Would you explain both these notes please.
    My Bad, date should have read 27/04/2016 (Sunday) not 37/04/2016 - sorry.

    Overtime can only be paid when hours are over 40 hours based on the day that they work overtime, so in the example I attached, it would have been the first entry, 1 hour to be paid as overtime from the department they worked additional hours.

    Does that make more sense?

  48. #48
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    My Bad, date should have read 27/04/2016 (Sunday) not 37/04/2016 - sorry.

    Overtime can only be paid when hours are over 40 hours based on the day that they work overtime, so in the example I attached, it would have been the first entry, 1 hour to be paid as overtime from the department they worked additional hours.

    If however the op works over 8 hours on more than 1 day, the system would need to apply overtime to each of these days once they have worked 40 hours or more. Didn't explain myself as this has only just been confirmed at my end.

    Does that make more sense?

  49. #49
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    I'm still not clear.

    Row 11 is a Mixing Operation with 8 hours.
    How do you calculate there is one hour overtime? Then what's the rule that says look at the Hygiene rate for that 1 hour O/T and not the Mixing rate?

  50. #50
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    I'm still not clear.

    Row 11 is a Mixing Operation with 8 hours.
    How do you calculate there is one hour overtime? Then what's the rule that says look at the Hygiene rate for that 1 hour O/T and not the Mixing rate?
    Because the O/T was obtained on the first entry (being Hygiene) as they worked more than 8 hours for that day so as soon as the op has worked more than 40 hours, the system should automatically add overtime for their records for that week in the last entry.

    If after the entry where overtime has been applied, they work another day pushing them (for example) into 50 hours, then the system needs to recognise that overtime has already been allocated from the previous 40 hours etc and then only add overtime for the hours work over 40 hours...

    It would be better if overtime was added to the LAST entry for that person in that week.

    Does this help?

  51. #51
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    It's becoming slightly clearer. So you're saying that O/T is any cumulative hours in a week which exceed 40 and not as at the moment any hours over 8 in a Day?

    However what determines which OT rate should be used? Is that always, as in this case, the Hygiene rate for the very first Operation Type worked in the week. Suppose on the 23/4/2017 that the Operation was a Goods Out, would the OT rate therefore have been £15.98?

    And another scenario. Suppose that it wasn't Sunday but say Sat 26/4/2017 with a QA Qualified Operation which created a cumulative for the week of 41 hours, ie. 1 hour OT. What rate would that be at. Still the Hygiene rate or the QA qualified of £15.84?

  52. #52
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    [QUOTE=Richard Buttrey;4640011]It's becoming slightly clearer. So you're saying that O/T is any cumulative hours in a week which exceed 40 and not as at the moment any hours over 8 in a Day?

    However what determines which OT rate should be used? Is that always, as in this case, the Hygiene rate for the very first Operation Type worked in the week. Suppose on the 23/4/2017 that the Operation was a Goods Out, would the OT rate therefore have been


    Yes you are correct, overtime is over 40 hours - not daily.

    Overtime should be determined from the day that they worked over 8 hours and paid at the rate in which area they worked in.

    For your last scenario, the rate would be £15.84 as they exceeded 40 hours as a QA Qualified - so 1 hour paid at £15.84

    Many thanks

  53. #53
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Overtime should be determined from the day that they worked over 8 hours and paid at the rate in which area they worked in.
    Clarification please

    When you say 'the area they worked in' do you mean the area they ARE working in ON THE DAY the OT is awarded? Your note in L11 is when you're working in Mixing yet it says you get the Hygiene rate. Why is that?


    In the last note you said it would be better if overtime was added to the LAST entry for that person in that week.
    As each daily record is added the system it will calculate the cumulative weekly hours worked and apply the relevant OT rate. That will then become a record in the database for that day. On subsequent days if OT is worked then those days too will record that particular days OT.

    Are you saying that the sum total of all those individual days OT must be removed from the database and added into the record for the last day?
    If so that of course raises the question as to how the system would know a day which was NOT the last day in the week was the last to be WORKED in the week since it has no future knowledge as to whether another future day will be worked.

  54. #54
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Clarification please

    When you say 'the area they worked in' do you mean the area they ARE working in ON THE DAY the OT is awarded? Your note in L11 is when you're working in Mixing yet it says you get the Hygiene rate. Why is that?


    In the last note you said it would be better if overtime was added to the LAST entry for that person in that week.
    As each daily record is added the system it will calculate the cumulative weekly hours worked and apply the relevant OT rate. That will then become a record in the database for that day. On subsequent days if OT is worked then those days too will record that particular days OT.

    Are you saying that the sum total of all those individual days OT must be removed from the database and added into the record for the last day?
    If so that of course raises the question as to how the system would know a day which was NOT the last day in the week was the last to be WORKED in the week since it has no future knowledge as to whether another future day will be worked.
    Yes, overtime is awarded to the area they are currently working in on the day the exceed 40 hours. (my notes may have been unclear - again).

    Overtime is only applied to an operative once they exceed 40 hours, this overtime can only be applied to the last entry can't it? - as modifying the database through VBA would make things more complex.

    Can you implement a solution to make overtime calculate correctly as per my brief as i don't know how to do it.
    As long as overtime is applied correctly and not under 40 hours or if the op works more than 8 hours in a day then it shouldn't be applied - only when 40 hours is exceeded then overtime is applied from the area that they worked in when exceeding 40hrs.

    Many thanks

  55. #55
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Yes, overtime is awarded to the area they are currently working in on the day the exceed 40 hours. (my notes may have been unclear - again).

    ....overtime is applied from the area that they worked in when exceeding 40hrs.

    Many thanks
    Sorry to belabour the point Luke but that statement does not appear consistent with the comment in L11 which says

    "This needs to be the 1 hour overtime obtained from the Sunday 23/04/2016 @ £13.23 based on the Hygiene rate even though they only worked 8 hours on this day and worked in Mixing room at a different rate. "

    L11 is the first day that the 1 hour OT appears yet you use the OT rate for Hygiene and not Mixing which is the operation on row 11.

    If you're saying that the comment in L11 is wrong then that's fine. Otherwise I still need to understand why you picked the Hygiene rate

  56. #56
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Sorry to belabour the point Luke but that statement does not appear consistent with the comment in L11 which says

    "This needs to be the 1 hour overtime obtained from the Sunday 23/04/2016 @ £13.23 based on the Hygiene rate even though they only worked 8 hours on this day and worked in Mixing room at a different rate. "

    L11 is the first day that the 1 hour OT appears yet you use the OT rate for Hygiene and not Mixing which is the operation on row 11.

    If you're saying that the comment in L11 is wrong then that's fine. Otherwise I still need to understand why you picked the Hygiene rate
    Not a problem Richard - I would prefer to have the OT awarded to the day in which they exceeded 8 hours (providing all other days worked were 8 hours).
    If the op then works 3 days at 9 hours and 2 days at 8 hours, things get even more complicated if they work in a different area on all 5 days.

    It would be preferred by management here if that they get paid the overtime on the lowest rate for the area that they worked in during the week. Example;

    2 days work in Mixing total of 18 hrs (example OT rate = £15)
    3 days worked in Hygiene total of 24 hrs (example OT rate = £13)

    Lets say this operative exceeds the 40 hours working in mixing room.
    This would mean that (based on the managements preference) that they would only be awarded the £13/hr overtime instead of the £15/hr.

    I was told however to just get it to work best possible so i would like to have the OT paid fairly so if you can apply OT from the days that they worked > 8 hours onto the last entry - that would be great.

  57. #57
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    One of your notes mentions a night shift. Are there different rates for a Sunday night shift and a weekday night shift, and indeed for Night Shift Pre & Post 12 weeks.

    If so would you add to the attached table and indeed confirm that this covers all operations and TIME periods which need to be considered.
    Attached Files Attached Files

  58. #58
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi Luke,

    One of your notes mentions a night shift. Are there different rates for a Sunday night shift and a weekday night shift, and indeed for Night Shift Pre & Post 12 weeks.

    If so would you add to the attached table and indeed confirm that this covers all operations and TIME periods which need to be considered.
    Hi Richard,

    Please see attached.

    Many thanks
    Attached Files Attached Files

  59. #59
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi,

    Just going out for the day, but that table doesn't have any Sunday night shift rates. Is that correct?

  60. #60
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Just going out for the day, but that table doesn't have any Sunday night shift rates. Is that correct?
    Yes that's correct, Currently no Sunday's are worked here.

    Thanks

  61. #61
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Just refreshing my memory on this after a week.

    Should overtime only start to accumulate and be paid when a total of 40 hours have been reached in a week. e.g
    Day 1 - 9 hours worked and paid at standard, no overtime paid
    Day 2 - 10 hours worked and paid at standard, no overtime paid
    Day 3- 10 hours worked and paid at standard, no overtime paid.
    Day 4 - 10 hours worked and paid at standard, no overtime paid
    Day 5 - 9 hours worked, 1 hour paid at standard, plus 8 hours at overtime rate (Cumulative 48 less 40)
    Day 6 - 5 hours worked, all paid at overtime

    If more than one area has been worked in the week then the overtime rate used for the day it is recorded will be based on the lowest area rate up to that day. If a subsequent day is worked in a lower rated area then no adjustment will be made to OT rates already used and logged in the database, only the last days overtime will be at the new lowest rate.

    Would you confirm please

  62. #62
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Just refreshing my memory on this after a week.

    Should overtime only start to accumulate and be paid when a total of 40 hours have been reached in a week. e.g
    Day 1 - 9 hours worked and paid at standard, no overtime paid
    Day 2 - 10 hours worked and paid at standard, no overtime paid
    Day 3- 10 hours worked and paid at standard, no overtime paid.
    Day 4 - 10 hours worked and paid at standard, no overtime paid
    Day 5 - 9 hours worked, 1 hour paid at standard, plus 8 hours at overtime rate (Cumulative 48 less 40)
    Day 6 - 5 hours worked, all paid at overtime

    If more than one area has been worked in the week then the overtime rate used for the day it is recorded will be based on the lowest area rate up to that day. If a subsequent day is worked in a lower rated area then no adjustment will be made to OT rates already used and logged in the database, only the last days overtime will be at the new lowest rate.

    Would you confirm please
    Hi Richard,

    Absolutely spot on mate - couldn't have put it better myself.

    Many thanks

  63. #63
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Thanks Luke,

    One other Q. Does the stuff we agreed in #62 also apply to a Sunday?

    i.e. If sometime during the week more than 40 hours have been worked, say 45 hours resulting in an OT calc of 5 hours, AND time had been worked on the previous Sunday, should ALL these 5 hours be paid at the Sunday OT rate, or still paid at the lowest rate paid in the week. I'm assuming the Sunday OT rate otherwise presumably there's no point in having a Sunday rate.

    And if they are paid at the Sunday Rate, should ALL 5 hours paid at the Sunday rate, or only those hours which exceeded 8 hours ON the Sunday. i.e. if the hours worked on a Sunday was say 10, would only 2 of the 5 hours be paid at the Sunday OT rate and the other 3 at the lowest rate yet paid in accordance with post #62?

  64. #64
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Thanks Luke,

    One other Q. Does the stuff we agreed in #62 also apply to a Sunday?

    i.e. If sometime during the week more than 40 hours have been worked, say 45 hours resulting in an OT calc of 5 hours, AND time had been worked on the previous Sunday, should ALL these 5 hours be paid at the Sunday OT rate, or still paid at the lowest rate paid in the week. I'm assuming the Sunday OT rate otherwise presumably there's no point in having a Sunday rate.

    And if they are paid at the Sunday Rate, should ALL 5 hours paid at the Sunday rate, or only those hours which exceeded 8 hours ON the Sunday. i.e. if the hours worked on a Sunday was say 10, would only 2 of the 5 hours be paid at the Sunday OT rate and the other 3 at the lowest rate yet paid in accordance with post #62?

    Yes, our week starts on a Saturday and finishes on a Friday - so if worked 5 hours on the previous Sunday and 45 hours throughout the week, 40 hours to be paid at the area rate, 5 hours to be paid in the overtime rate for the area and 5 hours to be paid at the Sunday OT rate.

    If however an operative works 5 hours on the Sunday and 38 hours through the week - they would get paid 40 hours at the area rate which they worked in for the week and then 3 hours paid at Sunday rate for the area.

    Does this clear things up?

    Cheers

  65. #65
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Yes, our week starts on a Saturday and finishes on a Friday - so if worked 5 hours on the previous Sunday and 45 hours throughout the week, 40 hours to be paid at the area rate, 5 hours to be paid in the overtime rate for the area and 5 hours to be paid at the Sunday OT rate.

    If however an operative works 5 hours on the Sunday and 38 hours through the week - they would get paid 40 hours at the area rate which they worked in for the week and then 3 hours paid at Sunday rate for the area.

    Does this clear things up?

    Cheers
    ...Proof of the pudding and all that.

    Try this version. Hoperfully it meets the requirement but obviously test and let me know where you spot any anomalies, and if you do upload the workbook in which you've added the data.

  66. #66
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    ...Proof of the pudding and all that.

    Try this version. Hoperfully it meets the requirement but obviously test and let me know where you spot any anomalies, and if you do upload the workbook in which you've added the data.
    Have you attached something?

    I can't see anything Richard?

  67. #67
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    ...I thought I had, but clearly not.
    Anyway here it is...
    Attached Files Attached Files

  68. #68
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    ...I thought I had, but clearly not.
    Anyway here it is...
    Hi Richard,

    There is a debug error when opening the pivot table report after adding a record - something to do with cache?

    Also, some of the calculations don't seem correct?
    See attached copy with yellow highlighted cells.

    Many thanks
    Attached Files Attached Files

  69. #69
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Yes, quite correct. T7 should never return less than zero. Change this to

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will correct K7 & M7

    S7 should have a <= not just a < SO change that to

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'd added some extra field names to the Input Record in G6, O6 & T6 but forgot to copy those labels into the field headers on row 12. If you copy those row 6 cells into the row 12 cells the PT should be OK.

  70. #70
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi Luke,

    Yes, quite correct. T7 should never return less than zero. Change this to

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will correct K7 & M7

    S7 should have a <= not just a < SO change that to

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'd added some extra field names to the Input Record in G6, O6 & T6 but forgot to copy those labels into the field headers on row 12. If you copy those row 6 cells into the row 12 cells the PT should be OK.
    Hi Richard,

    This has sorted the issues I posted above, however, there is quite a few calculation errors on it when I've tried it starting from Saturday to Friday.

    Have a look at the attached and see comments if you can?

    Many thanks
    Attached Files Attached Files

  71. #71
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Re the 3rd May OT. The entry on this day brings the cumulative hours to 41 for the week, i.e. 1 hour of overtime.
    During this week there was a 9 hour shift on Sunday 30th April. i.e. 1 hour more than the 8 hour normal day. Therefore shouldn't the 1 hour overtime to be recorded on the 3rd May OT be calculated at the rate of £16.93 meaning the Cost for the 3rd is £16.93 + £81.68 for the 8 hours @ £10.21. A total of £98.61? Which is what I've interpreted so far.

    If not can you explain why you use the £12.85 'normal' OT Rate. Or are you saying that the £16.93 is just the rate for hours worked on Sundays and all OT whether Sunday or not is at the 'Over 40' rate.

  72. #72
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hello Luke,

    Re the 3rd May OT. The entry on this day brings the cumulative hours to 41 for the week, i.e. 1 hour of overtime.
    During this week there was a 9 hour shift on Sunday 30th April. i.e. 1 hour more than the 8 hour normal day. Therefore shouldn't the 1 hour overtime to be recorded on the 3rd May OT be calculated at the rate of £16.93 meaning the Cost for the 3rd is £16.93 + £81.68 for the 8 hours @ £10.21. A total of £98.61? Which is what I've interpreted so far.

    If not can you explain why you use the £12.85 'normal' OT Rate. Or are you saying that the £16.93 is just the rate for hours worked on Sundays and all OT whether Sunday or not is at the 'Over 40' rate.
    Sunday rate is only payable if they exceed 40 hours on a Sunday.
    The main concern is that the Sunday rate was paid 5 times for that week.

    Just to clarify,
    The Sunday rate should change if the op works more than 40 hours:

    Op works 8 hours on sunday and 40 hours mon-fri = they get paid 8 hours of sunday rate + normal hourly rate

    op works 8 hours sunday and 35 hours mon-fri = they get paid 40 hours at normal rate + 3 hours sunday rate

    Does this make sense?

    Cheers

  73. #73
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    I'd noted the rate being paid 5 times and had identified the cause and corrected it on my working copy. Just hadn't mentioned it yet.

    I was specifically asking about the 1 hour overtime for 30th April which needs to be included on the first day's record where the cumulative hours exceed 40.
    In the example we're discussing 9 hours were worked on a Sunday.
    So using your example where you say
    op works 8 hours sunday and 35 hours mon-fri = they get paid 40 hours at normal rate + 3 hours sunday rate

    and given 9 hours are worked on a Sunday 30th April, doesn't this mean that on the 3rd May when the cumulative hours are 41 and 1 hour OT needs to be recorded, this 1 hour should be at the Sunday rate of £16.93 according to your rule.

    You describe an 8 hour Sunday where cumulative hours are 43 and indicate that 3 hours therefore must be at the Sunday rate, so why where 9 hours are worked on a Sunday where the cumulative is 41 shouldn't the first hour (the equivalent of your 3 hours) be at the Sunday rate.

  74. #74
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    I'd noted the rate being paid 5 times and had identified the cause and corrected it on my working copy. Just hadn't mentioned it yet.

    I was specifically asking about the 1 hour overtime for 30th April which needs to be included on the first day's record where the cumulative hours exceed 40.
    In the example we're discussing 9 hours were worked on a Sunday.
    So using your example where you say
    op works 8 hours sunday and 35 hours mon-fri = they get paid 40 hours at normal rate + 3 hours sunday rate

    and given 9 hours are worked on a Sunday 30th April, doesn't this mean that on the 3rd May when the cumulative hours are 41 and 1 hour OT needs to be recorded, this 1 hour should be at the Sunday rate of £16.93 according to your rule.

    You describe an 8 hour Sunday where cumulative hours are 43 and indicate that 3 hours therefore must be at the Sunday rate, so why where 9 hours are worked on a Sunday where the cumulative is 41 shouldn't the first hour (the equivalent of your 3 hours) be at the Sunday rate.
    Apologies, I'm understanding now.

    Yes, Sunday rate can only be applied (if worked on a Sunday) and their hours worked are greater than 40.

    So even if an op works 40 hours mon-fri and 8 hours on the sunday, the 8 hours must be paid at sundays rate.
    If the op works 8 hours on a sunday and 33 hours mon-fri, the will only get paid 1 hour at the sunday rate.

    cheers

  75. #75
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Try this one Luke
    Attached Files Attached Files

  76. #76
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Try this one Luke
    Hi Richard,

    Slowly getting there mate

    See comments on this version

    Cheers
    Attached Files Attached Files

  77. #77
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    In the overall calculation box you mention 40 hours @ £9.76

    Why £9.76 since this is the Pre 12 week rate and on the Variables your start date is 1/1/2017 putting you in the post 12 weeks slot which should presumably be £10.21.

    Would you clarify please

  78. #78
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi Luke,

    In the overall calculation box you mention 40 hours @ £9.76

    Why £9.76 since this is the Pre 12 week rate and on the Variables your start date is 1/1/2017 putting you in the post 12 weeks slot which should presumably be £10.21.

    Would you clarify please
    Apologies, yes at 10.21

    Thanks

  79. #79
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,
    Try this one
    Attached Files Attached Files

  80. #80
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi Luke,
    Try this one
    Hi Richard,

    Nearly there I think!

    See attached

    Thanks
    Attached Files Attached Files

  81. #81
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    ....Try the attached.

    Make sure you test with other combinations of stuff, like additional employees with pre 12 etc...
    Attached Files Attached Files

  82. #82
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    ....Try the attached.

    Make sure you test with other combinations of stuff, like additional employees with pre 12 etc...
    1 slight issue to fix on the example we are using and then it looks like all will be ok to test with multiple people

    see attached
    Attached Files Attached Files

  83. #83
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Can you explain the rule that says the the £30.63 in K18?

    I suspect you're saying that on the day it was entered, 14th May it was OK but then later in the week and because the hours exceeded 40, these 3 hour were added on 18 May in M14 as Sunday OT

    If so that means the system needs to go back and eliminate the K18 entry in those circumstances.

    Or what may be easier, although I've not thought yet, would be to reduce the M13 value by the £30.63 and show the difference of £7.92 on this day rather than the £38.55.

    Would that be an acceptable way?

  84. #84
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188
    Both ways are acceptable. Would be best if it could adjust the sunday value when exceeding 40 hours and then we can add a report to sow costs by Sundays.

    Is that okay to build??

    Cheers

  85. #85
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    The forum's playing up. I tried to edit the last post which should have started

    Can you explain the rule that says the £30.63 in K18 should not be there....

  86. #86
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Try this one.
    I've added some macro code to the macro that adds the record to the database.
    Now, if there is a Sunday recorded in the week and the Cumulative hours are >40 then the original entry is removed and the new record for overtime is used.

    On the attached clicking the add record will delete the value in K17. (This is controlled by a new column U which determines the row to adjust.

    So I believe this works as per this last test.
    However...
    The new record on the 19th and the Sunday record on the 14th are both 3 hours. But what should the adjustment be if these differ. Two scenarios.
    1. The 14th hours are say 3 hours and the 19th say 5 hours
    2. The 14th hours are say 5 hours and the 19th say 3 hours

    Let me know what the entries should be for both these scenarios.
    Attached Files Attached Files

  87. #87
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi Luke,

    Try this one.
    I've added some macro code to the macro that adds the record to the database.
    Now, if there is a Sunday recorded in the week and the Cumulative hours are >40 then the original entry is removed and the new record for overtime is used.

    On the attached clicking the add record will delete the value in K17. (This is controlled by a new column U which determines the row to adjust.

    So I believe this works as per this last test.
    However...
    The new record on the 19th and the Sunday record on the 14th are both 3 hours. But what should the adjustment be if these differ. Two scenarios.
    1. The 14th hours are say 3 hours and the 19th say 5 hours
    2. The 14th hours are say 5 hours and the 19th say 3 hours

    Let me know what the entries should be for both these scenarios.
    Hi Richard,

    Doesn't seem to work using the new scenario??

    See attached - cheers
    Attached Files Attached Files

  88. #88
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Morning Luke,

    Try the attached.

    Rgds
    Attached Files Attached Files

  89. #89
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Morning Luke,

    Try the attached.

    Rgds
    Hi Richard, getting there.

    On the attached example, on the 18th, they exceeded 40 hours and that meant the 2 hours was paid as overtime from sunday.

    However, because the op worked on Friday another 3 hours, this means they have still exceeded 40 hours and this means that the full 5 hours is to be paid at sundays rate.

    If the op worked 4 hours instead of 3 on the 19th, then 5 hours will be paid at sundays rate and the 1 hour to be paid at standard overtime rate.

    Make sense?


    Cheers
    Attached Files Attached Files

  90. #90
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    See the attached.

    One of the difficulties with the current input data/check results/correct if necessary process we're going through is that an adjustment for the latest data input can compromise and earlier data input.

    If this doesn't work then we'll need to write down the rules for all permutations and recode the system. e.g.

    1. If a weekday and cumulative hours <=40 then do this
    2. If a weekday and cumulative hours <=40 and previous cumulative <=40 and there are hours for a previous Sunday then do this
    3. If a weekday and cumulative hours <=40 and previous cumulative <=40 and there are no hours for a previous Sunday then do this
    4. If a weekday and cumulative hours >40 and previous cumulative <=40 and there are hours for a previous Sunday then do this
    5. If a weekday and cumulative hours >40 and previous cumulative <=40 and there are no hours for a previous Sunday then do this
    6. If a weekday and cumulative hours >40 and previous cumulative <=40 and there are hours for a previous Sunday then do this
    7. If a weekday and cumulative hours >40 and previous cumulative >40 and there are hours for a previous Sunday then do this
    8. If a weekday and cumulative hours >40 and previous cumulative >40 and there are no hours for a previous Sunday then do this
    9. If a Sunday then do this

    ...etc.
    Attached Files Attached Files

  91. #91
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hi Luke,

    See the attached.

    One of the difficulties with the current input data/check results/correct if necessary process we're going through is that an adjustment for the latest data input can compromise and earlier data input.

    If this doesn't work then we'll need to write down the rules for all permutations and recode the system. e.g.

    1. If a weekday and cumulative hours <=40 then do this
    2. If a weekday and cumulative hours <=40 and previous cumulative <=40 and there are hours for a previous Sunday then do this
    3. If a weekday and cumulative hours <=40 and previous cumulative <=40 and there are no hours for a previous Sunday then do this
    4. If a weekday and cumulative hours >40 and previous cumulative <=40 and there are hours for a previous Sunday then do this
    5. If a weekday and cumulative hours >40 and previous cumulative <=40 and there are no hours for a previous Sunday then do this
    6. If a weekday and cumulative hours >40 and previous cumulative <=40 and there are hours for a previous Sunday then do this
    7. If a weekday and cumulative hours >40 and previous cumulative >40 and there are hours for a previous Sunday then do this
    8. If a weekday and cumulative hours >40 and previous cumulative >40 and there are no hours for a previous Sunday then do this
    9. If a Sunday then do this

    ...etc.
    Hi Richard,

    When adding a record for 13/05/2017 I get a "type mismatch" error on the below code:

    If Range("Sunday_Adjustment") > 0 Then

    Can you have a look?

    Thanks

  92. #92
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke

    Wrap the V7 formula in an IFERROR() function. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    also copy this to V1 in case the formula is ever deleted.

    In general should you ever come across a cell with a formula that returns #N/A wrap it in an IFERROR and default it to a zero - or indeed any value that will be relevant.

  93. #93
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hello Luke

    Wrap the V7 formula in an IFERROR() function. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    also copy this to V1 in case the formula is ever deleted.

    In general should you ever come across a cell with a formula that returns #N/A wrap it in an IFERROR and default it to a zero - or indeed any value that will be relevant.

    Now the total costs doesn't work?

    See attached

    Thanks
    Attached Files Attached Files

  94. #94
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Now the total costs doesn't work?

    See attached

    Thanks
    It appears that when using the Add Record button, the coding is removing the formula from the Total Cost and Operation Cost cells.

    I'm unsure which line of code is doing this..

    Can you assist?
    If myself or others have helped, please add to our reputation by pressing the 'Star' icon below this.

  95. #95
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Sorry I was out most of yesterday and this morning.

    I've decided to change the formulae somewhat and use some additional macro code to help the process and will probably have something later today.

  96. #96
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Sounds great - let me know as soon as!

  97. #97
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    I found another anomaly last night which I need to correct. It will be later today now as I'm going out shortly.

  98. #98
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Thanks Richard - enjoy the sunshine!

  99. #99
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke

    See attached. Hopefully this is what you want.

    Here's a brief precis of what it does and how I have understood the rules.

    Up until a total of 40 hours have been worked in a week commencing on Saturday the Cost will be the number of hours worked on that day at the appropriate standard rate from the Rate matrix

    Once the total hours are >40 then the OT rates from the matrix will be used.
    If there has been a Sunday shift then the overtime hours up to the hours worked on the Sunday will be added in the Sunday OT column FOR THE SUNDAY row. Any OT hours over and above the Sunday OT hours will be in the Normal OT hours on the CURRENT ROW.

    The Grouping buttons above the worksheet may be use to hide the columns if necessary. Group 1 will show just the Inputs, Group 2 will show Inputs and the Amounts, Group 3 will show all columns including the working columns.

    Don't change anything on the hidden rows 1:3

    I've left my test data in so you can either add new test data or just delete all rows below Row 14 and start afresh.
    Attached Files Attached Files

  100. #100
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Good Morning Richard,

    Thanks for persevering with me on this one.

    Seems that you have got it extremely close to the requirement.

    Please see attached and colour coded calculations.

    Regards
    Attached Files Attached Files

  101. #101
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Re the blue person 3

    Here we have 40 hours worked from 3 - 7 June
    On the 8th June 9 hours are worked hence they're all OT. On the previous Sunday 4th June, 8 hour were worked, so shouldn't 8 of the 9 hours be paid at the Sunday rate of £16.93 and 1 hour at the Std OT rate of £12.85

    Hence
    40 @ 10.21 = 408.4
    8 @ 16.93 = 135.44
    1 @ 12.85 = 12.85

    Total £556.69.

    If not what's the rule here and why do you ignore the £12.85

  102. #102
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Yes you are correct - my formula wasn't extended to P42.

    The 'blue' person is correct

    Thanks

  103. #103
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Ok I thought that was probably the case. For the two others I'd forgotten about there being rates for night shifts.

    The attached is now corrected. I added the same three sets of records - starting in different weeks of course which you'll see in row 13:30

    Regards
    Attached Files Attached Files

  104. #104
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    This seems to be fully working now!

    I will continue testing it but for now - please enjoy the added reputations and relax

    Thanks

  105. #105
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Not for long though - see attached.

    Look at the green highlight cells and red cells not showing a cost even though they worked for that week.

    Cheers
    Attached Files Attached Files

  106. #106
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Since the 8 hours you added on the 16 June were all OT and since 8 hour were worked on the previous Sunday those 8 hours have been added to Sunday 11 June - Cell K25, hence the current row shows zero cost.

    Whenever OT is worked and there are still "unused" Sunday hours, the OT is first added to the Sunday row, any additional hours worked above the Sunday hours are included on the current row at the normal OT rate. If you check the PT Report for yourself for the W/C 10 June you'll see the total is £543.76, the same as the sum of K22:K26

    In checking this I saw where I could simplify some formulae so have done so with the attached which is essentially the same as the previous version.

    Rgds
    Attached Files Attached Files

  107. #107
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Somethings still not quite right - please see attached and refer to yellow highlighted cells.

    Also, can you make it show the cost per day on the pivot table - this may be needed when running further reports.

    EDIT

    The difference is coming from cells N18 & L18 (difference of £12.08) - this is the difference between the manual calcs of £567.16 and the spreadsheet cost of £579.24

    This should be an easy fix, however - would this then affect other scenarios?

    Many thanks
    Attached Files Attached Files
    Last edited by LukeGilfoyle; 05-15-2017 at 08:18 AM.

  108. #108
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    See edited post above - thanks

  109. #109
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    See if the attached is OK.

    I've not added the cost/day to the PT yet since I wasn't sure which field you were referring to. Is that Not the Total Cost column K?
    Attached Files Attached Files

  110. #110
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    All working now Richard - will continue testing.

    We briefly spoke about protecting the workbook in the earlier posts - i would like this sheet protected to secure all of the hard work and coding.
    As the macro uses cut/paste method - with the sheet being protected, it throws this error when i've hidden the 2 groups and protected the sheet: "Range("Data").Cells(2, 1).EntireRow.Insert"
    I thought this may happen - is there a way around?

    ALSO, the "Start Date" isn't loading info from the Variables tab anymore.

    Yes, for PT the total operation cost needs to be shown for that date worked with number of hours - how the pivot table is at the moment seems okay.

    Many thanks for your help! - plenty of rep left for you

    EDIT

    Can you add in another column to the right of "Record Date" in column "B" which is =weeknum(B7) please? I would like to run some pivot charts off of the weeknumbers.

    Not sure how to amend the coding to allow that!

    1 Final thing i've spotted is that if the operation type is "Nights FLT" but the period is set to "Day" - it will throw an error in L7 - is there anyway this can be validated to stop the "add to database" button from inserting into the database? Either by showing an error message or by automatically recognising the mistake and highlighting the error and possibly providing a simple userform to amend this mistake???

    Or, a simpler way if you know of one! - needs to be simple though! haha

    thanks
    Last edited by LukeGilfoyle; 05-16-2017 at 03:46 AM.

  111. #111
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Yes, at the moment protecting the worksheets will prevent the add a record macro running. It's a simple change to make but in my experience this change is best left to the very end when we know everything else is working.

    The Start date formula seems to have gone missing several versions ago! Add the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    to H7 and copy it to H1.

    Re the week number. Not sure whether you mean an actual number, like say Week 10, Week 11?
    However doesn't the Slicer that shows the w/c date give you that functionality? It shows the w/c date rather than a number like say 13 but it is essentially identifying the same information. You can select a single w/c date or a number.

    The other way, which does the same as the slicer would be to right click the Record Date column in the PT, Choose the Group option, select 'Days' and at the bottom specify the number 7. This will change the look of the Record Date so that it shows text like
    13/05/2017 - 19/05/2017


    Yes the Nights FLT is showing an error since the Rate Matrix on the Variables shows N/A. Change that to zero or blank. Yes I can add another check similar to the one that warns that not all the 6 yellow cells have been completed. I'll add that in when doing the final correction for the Sheet protect and any other changes. I'd like to make sure the actual calculations are all returning the right answers before finalising the look & feel.

  112. #112
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    You're right - however, see attached issue with different hourly rates.

    Thanks
    Attached Files Attached Files

  113. #113
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    I'm currently calculating £470.41 vs your £467.5

    The difference is the 3 hours Line Leaders NORMAL time worked on the Sunday. I'm using the line leader rate of £10.73, your calc uses £9.76 which is the lowest operation rate in the week.

    I have previously understood that it's only Overtime calculations that use the lowest OT rate. Are you saying that there's a similar rule for NORMAL time?

  114. #114
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    I'm not getting that:

    40 hours worked mon-fri @ 9.76 (pre 12 weeks) = £390.4
    3 hours worked saturday as OT @ 12.85 = £38.55
    and then 3 hours work sunday as line leader (same Sunday rate as std OT) @ 12.85 = £38.55

    £467.5

    Does this make more sense?

  115. #115
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    No I understand the calculation. After I've corrected it (originally it was the 3 hours that was appearing in the Sun OT column and not the calculated cost) I get the £470.41 I mention. The difference to your number is the 3 hours NORMAL Time that happened to be on the Sunday.

    The only question is the rule as to which NORMAL Rate to use for any shift.
    As I said I had understood that it was only OVERTIME calculations that should use the lowest OT rate for that person where they had two or more different operational shifts in the week.
    Your calculation indicates that the Rate to be used for ANY NORMAL time should also be at the lowest rate in the week. In this case the lowest rate for Packing & indeed Mixing is £9.76 which is less than the Line Leaders normal rate of £10.73. Is that correct?

    If so then it implies that if the last operation in the week is at a lower rate than all previous days then the system needs to recognise that.
    Either by adjusting the latest day (and in extremis this could show a negative adjustment for that latest da), or going back to all previous days and recalculating each one using the lowest rate.

    If the Lowest rate has to be used for all NORMAL shift calculations it would be simpler if the adjustment was made to the latest day rather than having to go back and adjust all the previous days.

  116. #116
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard that makes sence - understand it now.

    So what would happen if the Op who normally works in packing @ 10.21 get if they worked 1 hours overtime as a line leader 9.76 on the previous Saturday?

  117. #117
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    ...and further to that last one since the forum doesn't seem to allow editing at the moment the difference between us of £2.91, your £467.5 and my £470.41 is the 3 hours at the difference between £10.73 & £9.76 for Line Leader and Mixing/Packing respectively. i.e. 3 x £0.97

  118. #118
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    No wait, i don't understand,

    40 hrs @ 9.76 = £390.4
    3 hrs STD @ 12.85 = £38.55
    3 hrs SUN @ 12.85 (same OT rate) = £38.55

    I don't understand what you mean about normal rate?

  119. #119
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Hi Richard that makes sence - understand it now.

    So what would happen if the Op who normally works in packing @ 10.21 get if they worked 1 hours overtime as a line leader 9.76 on the previous Saturday?
    Hi,

    I think we need to distinguish the rule for Normal Time and the Rule for Overtime and any associated adjustment to be made.

    The rule at the moment is that Overtime is always costed at the Lowest OVERTIME Rate for any operation the Op has worked.
    At the moment there is no exception rule for Standard Time and this is costed at the rate for that operation on that day - up to the 40 hours.

    In the example you cite the Op will be paid for the normal day at 10.21 up to 40 hours, and if that shift included hours > 40 then the rate used will be the lowest OT rate for Packing and Line Leader. They happen to be the same £12.85 so it's perhaps a bad example. However if the Line Leader OT rate was say £11.95 then that would be the rate at which they were paid.

    The system can be made to treat it however you want.
    The only Q at the moment is that relating to the STANDARD Hours. Putting this in another way are these to be paid at the rate for the operation worked, so if there's a mix then the hours will be costed accordingly and one 8 hour shift will be costed differently to another 8 hour shift,
    OR
    must all the 40 hours be at the lowest STANDARD rate in a similar way to the OT Calc?

  120. #120
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Standard hours to be paid based on the departments worked in, 24 hrs in mixing @ 10.21 and then 16 hrs as yard man @ 9.76 -- rate needs to be paid based on dept.

    Standard OVERTIME can be paid at lowest OVERTIME rate from that week.

    Any SUNDAY OVERTIME must be paid at SUNDAYS rate,.

    Hope this clarifies?


    Many thanks,

  121. #121
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Thanks Luke,

    That's how the system will currently behave as far as OT is concerned.

    The Question is which 40 hours and rates should be used for reckoning the standard costing. Taking a strict chronological approach using your example the first 40 hours were as follows

    Day 1 - 3 @ 9.76 - Packing
    Day 2 - 3 @ 10.73 - Line Leader
    Day 3 - 8 @ 9.76
    Day 4 - 8 @ 9.76
    Day 5 - 8 @ 9.76
    Day 6 - 8 @ 9.76
    Day 7 - First two at 9.76 = 40 hours standard, which is where the 6 hours OT kicks in 3 @ Sunday 12.85, and 3 @ Stand. Ot 12.85

    That's what the current system is doing. However your last example reckoned all 40 hours @ 9.76 hence the difference for the 3 hours at 10.73 vs the 3 @ 9.76.

    In other words is the rule that all 40 hours must be at the lowest rate irrespective of the actual operation worked on. Consider for instance exactly 40 hours worked at a mixture of operations (i.e. no OT involvement), do you disregard any operations which carry a higher rate?

  122. #122
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Because Day 2 was a Sunday - the 10.73 for 3 hours in my manual calcs is totally disregarded as it is applied at 12.85 (SUNDAY OT RATE) and not Std Rate because it's 3 hours greater than 40 (3 hrs Sun OT @ 12.85 and then 3 hrs STD OT @ 12.85).

    If this was PACKING, it would obviously be the 16.93 for SUNDAY - just making you aware that i notice the OT rates are the same in my current example.

    Cheers

  123. #123
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Thanks Luke

    Take an example where 3 hours was worked on the Sunday but there were only 42 hours worked in the week.

    Obviously the 2 hours would be paid at the Sunday OT rate but what about the 1 hour standard rate that's still 'left' on Sunday?

    Should that be at the Standard Operation rate for whatever the Sunday Operation is even if that was then highest rated operation in the week, or at the lowest operation rate anywhere in the week?

  124. #124
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    That should be the standard operation rate for the Operation that was ran on Sunday.

    Many thanks

  125. #125
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Morning Luke,

    See how you get on with this one
    Attached Files Attached Files

  126. #126
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Because the OT happened on the Friday for 1 hour - only the 1 hour OT is to be paid at Standard OT for that area - not the Sunday Rate.

    Sunday Rate can only be applied if 40 hours have been worked Mon-Fri (just found out from management when showing them a few tests on the attached version)

    If the op works 8 hours x7 days, then yes, the Sunday rate and Standard OT is applied.

    See attached version and manual costs

    Many thanks
    Attached Files Attached Files

  127. #127
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Are there any other exceptions to the wage rate rules that we've discussed thus far?

    For instance are there any Bank Holiday exceptions or special rules for Saturdays?

    In addition your statement that "only the 1 hour OT is to be paid at Standard OT for that area " seems at odds with the current rule that says Standard OT is paid using the lowest rate for any of the areas worked in the week. Would you clarify that too please.

  128. #128
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by Richard Buttrey View Post
    Hello Luke,

    Are there any other exceptions to the wage rate rules that we've discussed thus far?

    For instance are there any Bank Holiday exceptions or special rules for Saturdays?

    In addition your statement that "only the 1 hour OT is to be paid at Standard OT for that area " seems at odds with the current rule that says Standard OT is paid using the lowest rate for any of the areas worked in the week. Would you clarify that too please.
    Hi Richard,

    There are NO bank holiday/Saturday Exceptions for Agency staff.

    The OT is only paid as previously discussed; Over 40 hours (excl. Sunday) uses STD OT rates.
    Over 40 hours (incl. Sundays) can only use the Sunday OT rate if 40 hours have been worked Monday-Friday.

    Can the 'lowest rate rule' be scrapped please? Management changed their minds on this and failed to inform me.

    (This is why i struggled to explain the rules as to be honest - i don't think anyone really knows haha)

    The way this is built, is the new way of working - once the above rules are applied, it should be fine.


    Thanks

  129. #129
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hi Luke,

    Check the following which hopefully picks up the latest understanding.
    Attached Files Attached Files

  130. #130
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Morning Richard,

    Looks like there is only 1 more thing to correct.
    In my example attached, see comments - many thanks
    Attached Files Attached Files

  131. #131
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Why are you using £13.23 for the 3 hours of Standard OT calculation?

    The OT rate for a QA trainee is £15.84, the £13.23 is for the Cleaning Line.

  132. #132
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Because 40 hrs worked mon-fri (8 hrs/day)

    3 hours was worked OT on the Saturday @ 13.23

    Cheers

  133. #133
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Luke,

    This is introducing a new dimension now that you've dropped the rule about using the minimum OT rate for any shift worked.

    What if 8 hours OT was racked up on the last day , there was no Sunday hours worked and and the hours worked in the earlier days in the week were 3,4,1 - all on different operations with different OT rates?

    Does that mean that the total OT has to be calculated by using all three sets of rates?

    And I can see another potential complication. Suppose only 6 hours OT with the same hours as above on different operations. Which 6 hours should be used?
    All the 3 hours from the earliest day plus 3 of the 4 hours in the 2nd day
    2 of the 3 hours from 1st day, plus all the 4 hours from the 2nd day?
    1 of the 3 hours from 1st day, plus all the 4 hours from the 2nd day plus the 1 hour on the 3rd day?
    i.e. what is the precedence when it comes to using the hours? And finally do any hours worked on a Sunday still take priority for allocating OT?

  134. #134
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    It is highly unlikely that any Agency staff will ever work Sundays/Weekends or do any OT.
    I wanted to get it to work fully in case the business decides to use the same sheet for Contracted staff.

    But:

    If someone works Mon-Fri x8 hrs/day - they will be paid 8 hours at the different rates/operations.

    If someone works Mon-Fri (4 days @ 8 hrs/day +++ 1 day @ 9 hrs) then they would be paid the 1 hour STD overtime from the day the worked 9 hours.

    If someone works Sat for 4 hours, Sunday for 3 hours and then 40 hours Mon-Fri in all different operations - they will be paid 40 hours at each std operation cost, 3 hours at Sunday Rate and 4 hours Std OT for the area worked on the Saturday.

    If someone works Mon and Tues for 9 hrs/day with 2 different rates and then Wed-Thursday for 22 hrs - they will be paid 22 hrs @ the operation std rate ++ 16 hours standard operation rate ++ 2 hours STD OT for the areas worked in.

    Make sense?


    Thanks

  135. #135
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke.

    The calculation of Standard hours is straightforward. Up until 40 hours have been worked then whatever the hours for the day is multiplied by the rate for the day.

    What I'm concerned about is once 40 hours have been racked up (assuming Monday - Friday) what rate should be used for each subsequent hour?
    Take the example you gave

    If someone works Mon-Fri (4 days @ 8 hrs/day +++ 1 day @ 9 hrs) then they would be paid the 1 hour STD overtime from the day the worked 9 hours.
    That 1 hour OT will use the rate for the 5th day, the one they worked 9 hours. Yet that is different to the example in the last workbook you sent. There there were 7 hours OT.
    So the first 4 hours were applied to the Sunday 4 hour shift worked and hence the Sun OT rate was used (and I'm assuming that it will always be the case that Sunday OT takes priority)

    However your calculation shows that the remaining 3 hours OT should be based on the first Saturday Cleaning Line operation (which just happened to also be a 3 hour shift). But that's different to the statement above which says the OT rate to be used is from the day on which the OT started to accrue, i.e the latest day being Friday.

    So I'm still confused.

  136. #136
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    If the 1 day @ 9 hrs was the Monday - it would need to pay the 1 hour OT from the operation on the Monday.

    Sunday OT is only paid if Mon-Fri is > 40 hours.

    If Mon-Fri = 39 hrs and Sunday worked 3 hours = 40 hrs paid @ std rate + 2 hours Sun OT

    I understand your confusion - but because 40 hrs was worked mon-fri in the example attached earlier, the OT rate used is that from the 3 hours on Saturday

    Make more sense?


    Thanks

  137. #137
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    I suppose the question is if there is overtime to cost and there is more overtime than is worked in a single day and where different operations have been worked, which rates should be used. I assumed from your earlier workbook example that you started at the earliest day, i.e. Saturday and because the hours worked of 3 equalled the OT to allocate you used the Saturday rate.

    But take your workbook example of 7 hours OTand suppose instead of 7 hours it had been 9 hours. How would you calculate the OT in that case?
    I understand that Sunday OT is always apportioned first so that would be 4 hours of the 9 used up.
    Presumably you'd then use 3 hours from Saturday as in the workbook example (since 3 hours were worked on the Saturday) leaving 2 hours to allocate.

    Which rate would you use for those next 2 hours. Would you use whatever the rate was for the next day, i.e. Monday (The processing Days operation)?

    And in general should the overtime hours be allocated in the following order, Sunday, Saturday, Monday, Tuesday, Wednesday, Thursday, Friday?


    As I said I was confused sine your post #134 was saying that after the Sunday OT is allocated you start with the latest day and not the earliest day as in the workbook example. If you confirm the order of allocation above then presumably your '134 was wrong.

    Sorry to belabour the point but I still don't understand the rule.
    If the system does need to count back to find the relevant daily OT rates to use then it is inevitably going to complicate the process. The earlier rule of always taking the lowest OT rate worked was reasonably easy to deal with but if the above is the way things should happen and because of various permutations of hours worked and OT hours to allocate then I'm going to have to think about it a bit more.

  138. #138
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    This is all getting very confusing now!

    You are correct about Sunday's providing they have worked 40 hours.
    if it's 38 hours mon-fri = 2 hours Sun OT

    What i will do now is take V8 to the HR dept. and show them what it's doing again - if they agree with the costs then it will be okay.

    I need to get full confirmation on how it currently works before i try and explain again.

    I will post back tomorrow,

    again, thanks for all your help!

  139. #139
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    OK Thanks Luke.

    I'm attaching a simple (hopefully) example of the scenarios under discussion. It doesn;t cover the calculation of normal time since I think we're on the same page with that. It sets out the various permuations for calculating OT. Basically do you count forwards when allocating OT to operational rates or backwards.
    Attached Files Attached Files

  140. #140
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Morning Luke,

    I was thinking about this overnight.

    The fundamental question and conundrum hat HR should answer is the following.

    When OT is to be paid because Mon-Fri hours are >40, and assuming two people work exactly the same number of hours on the SAME days on the SAME operations with the ONLY difference being that two of the operations worked (which carry two different OT rates) on two of the days are swapped around, this will mean that the two people will earn a different amount for the week which surely can't be right.

    This occurs because as we understand at the moment OT is allocated in order of the hours worked on the days calculating coming forward

    Take a simple example to make the point. No Saturday or Sunday working and 9 hours worked each day Mon - Friday meaning 5 hour OT to be paid.
    One person works a packing shift on Monday and Hygiene Shift on Tuesday. They would earn OT at £12.85 for the Packing shift.
    The other person works a Hygiene shift on Monday and Packing Shift on Tuesday. They would earn OT at £13.23 for the Hygiene shift.

    Therein is the nub of the problem - which didn't exist when OT was paid at the lowest rate earned in the week.

  141. #141
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Different areas receive different rates which is absolutely fine.
    Spoken to management this morning and discussed the example in V8.

    If an op works 37 hours mon-fri and 3 hours on saturday and 2 hours on sunday they will get paid 37 + 3 hours saturday and then 2 hours sunday rate.

    Sunday always has priority over std OT.

    If an op works 38 hours mon-fri and 3 hours sunday they will get paid 40 hours std + 1 hour sunday.

    The system needs to make sure that basic hours and STD OT are added up to 40 hours before any OT occurs. It should always add up STD OT and Saturday OT before any Sunday is paid.

    If 1 hours worked on saturday and 2 hours worked on sunday and 38 hours worked mon-fri then the system needs to make sure that it's 40 hours before any OT is paid.
    For this example, it needs to do 38 basic ++ 1 hours saturday (FIRST) ++ 1 hours STANDARD from Sunday ++ 1 hours SUNDAY OT.


    Does this make sense?


    Thanks

  142. #142
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    If there is no saturday and sunday worked but 8 hours worked mon-thursday and 10 hours worked on fri - it's easy.

    5 days x 8 hrs @ std rate ++ 2 hours STD OT

    Thanks

  143. #143
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Did you get an answer to the conundrum I posed where two individuals receive different earnings for the SAME mixed shifts and hours worked during the week WILL receive different earnings

    Suppose NO Saturday or Sunday working and 9 hours worked each day Mon-Fri meaning a payment of 5 hours OT is due. Which OT rate should be used for the 5 hours? The OT rate for the first operation worked in the week - i.e. Monday? Which of course means that two people working the same two operations but with the Monday & Tuesday operations swapped around are paid differently. Is that what management really want??

  144. #144
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Would you be a legend and post me the examples you mentioned in the above post (#143)?

    I can't visualise what you mean so i cant answer.

    Many thanks

  145. #145
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    If there is no saturday and sunday worked but 8 hours worked mon-thursday and 10 hours worked on fri - it's easy.

    5 days x 8 hrs @ std rate ++ 2 hours STD OT

    Thanks
    We're obviously crossing in the post!
    But which hourly RATE for those 2 hours? The rate appropriate to the Monday operation presumably? Suppose I work a Hygiene shift on Monday and you work a Packing shift and then on Tuesday we swap around. Are you happy that you get less than me?

    I don't think it is easy unless management want strife since those 2 hours could be paid at a different rates for two people working EXACTLY the same hours and operations albeit on different days.

  146. #146
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Now i'm understanding.

    For the example you quoted above - the 2 hours are to be paid for the operation where they worked 10 hours (2 hrs OT)

    It's tough luck if 2 people work 42 hours in a week, one works in hygiene and one works in packing and on the last day they swap areas (HIGHLY UNLIKELY) meaning 1 person gets paid less than the other.
    It would be on the supervisors back if they did this, not the spreadsheets.

    Or am i missing the point here?


    Cheers

  147. #147
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Hi Richard,

    Would you be a legend and post me the examples you mentioned in the above post (#143)?

    I can't visualise what you mean so i cant answer.

    Many thanks
    Hi Luke

    See attached
    Attached Files Attached Files

  148. #148
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Now i'm understanding - how easy is it to make that correct?? i.e. both be paid the same??

  149. #149
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Quote Originally Posted by LukeGilfoyle View Post
    Now i'm understanding - how easy is it to make that correct?? i.e. both be paid the same??
    Hello Luke,

    Ultimately most things are possible but to do that would be extremely complex since it raises all sorts of permutational comparisons, and to develop such a system would take a significant amount of time.
    The first thing to decide would be which rate to choose? The first day or the second? Then, and just to make the point the example I gave only considered two people. But in practice it would need to use the current record being entered and consider ALL the people who have already been recorded in that week in order to ensure there were no inconsistencies, and obviously the more people the system has to consider the greater the number of permutations of operations worked on different days but with the same number of hours.

    Even that only considers the current week, but conceptually why should two people who happen to work the same operations but in two different weeks not be subject to the same 'fairness' calculation? And in fact if you think about this the same person might work the same operational pattern in two different weeks with two operations swapped around and they'll be paid differently week on week.

    The more I think about this the more I believe that the original idea was the best and certainly the fairest. If I was trying to sell this to management the approach and explanation I'd adopt is as follows.

    Even were it practical to compare two people to ensure that for similar operational hours they were paid the same, it means that rate used for the OT calc will always be the rate applicable to the first person's time sheet that happens to have been recorded. That is quite arbitrary and means that the rate to be used for both could be either the Highest or the Lowest (of two different operations). It all hinges on the first record entered in the system which is arbitrary.
    i.e. in advance there is no certainty of what will be paid overall and it's just a chance as to which rate will be used. That doesn't seem to me a sensible business model.

    So given that it's chance and quite arbitrary the simplest approach is to do as we started out and make the rule "always use the lowest (or highest) of the rates for any of the shifts worked in the week".

    That way it is certain that two people working the same operations & hours albeit on different days will always be paid the same and it avoids the complexity of having to compare against all other people's time sheets.

    The rate used could be either the highest in the week, the lowest in the week or even the average of all the rates in the week.
    The big plus point is that whichever rate is used, it is known with certainty at the time of the data entry and the system doesn't need to work back and find which days are due to be used for identifying the operation and hence the OT rate (or rates if OT has to use two or more previous days).

    I'd urge you to convince management that this is the most sensible and pragmatic approach and is just about ready to introduce. All that's needed is to re-introduce the min/max/average rate rule.

    Let me know.

  150. #150
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hi Richard,

    Can you put the min rule back on and i'll show management again and make sure that OT is calculated correct?

    I'll have to see if it's okay with them

    Thanks

  151. #151
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Allocating hours to correct deparments using formulas

    Hello Luke,

    Here it is with any Standard OT set to use the minimum rate for any of the operations worked Saturday - Friday. Sunday takes precedence as before.

    If it were me presenting it to management I'd point out that the first principle of any wage calculator is that the results should be CONSISTENT and CERTAIN.
    Otherwise if there is anything arbitrary is has the potential to create ill-feeling with possible consequential effects on productivity.

    As explained earlier the previous 'rule' produced inconsistent results.
    Attached Files Attached Files

  152. #152
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: Allocating hours to correct deparments using formulas

    Hello Richard,

    I have been on Holiday these last 2 weeks and had no access to the internet - it was wonderful!

    I have sent the sheet onto HR for further testing and will update ASAP.

    Many thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] working hours time not correct if before 08:00
    By steve400243 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2017, 01:37 PM
  2. Replies: 4
    Last Post: 01-06-2016, 08:24 PM
  3. Correct format to display business hours/days
    By M.e.R. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-28-2015, 10:09 AM
  4. [SOLVED] Calculating Time Differences and Allocating Hours
    By blaze182 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2014, 07:50 AM
  5. [SOLVED] Adding of the 9 Hours in Time (In Text Format) with correct
    By thilag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-10-2013, 04:50 AM
  6. Allocating OT Hours - SOLVED
    By tcowen61 in forum Excel General
    Replies: 1
    Last Post: 11-17-2008, 06:44 PM
  7. Replies: 3
    Last Post: 05-17-2008, 05:25 AM

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