+ Reply to Thread
Results 1 to 8 of 8

Differing pay rates for multiple employees in a job costing spreadsheet?

  1. #1
    Registered User
    Join Date
    07-27-2021
    Location
    UK
    MS-Off Ver
    16.51 for Mac
    Posts
    4

    Differing pay rates for multiple employees in a job costing spreadsheet?

    Sorry, new on the block so hopefully not breaking any rules already (!)

    I'm a cost administrator for a construction company. I keep costing spreadsheets for each building project with tabs for employee costs, professional fees, suppliers etc

    The employee costs are in a table with the date (week ending) heading the columns and the employees listed in rows, their weekly hours are inserted into that table.

    Now however I've got a situation where some employees pay rates are staying the same, some are going up and some are going down, this is all happening on different dates for different employees. So this simplified table doesn't seem fit for purpose anymore.

    I am racking my brains on how to do this simply but I'm struggling. Has anyone had to do this before? I imagine it's common but might be wrong!

    Any help would be greatly appreciated!

  2. #2
    Forum Moderator 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
    28,420

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Hi and welcome to the forum

    You probably need another three column table with columns for Employee, Date from which pay applies, Pay Rate
    Then add information for each employee

    In the table that calculates the cost for any week it will need a formula to interrogate the table mentioned above.

    Upload the workbook you have and manually add some examples of what you expect to see for different weeks and no doubt we can put the formulae together.
    Richard Buttrey

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

  3. #3
    Registered User
    Join Date
    07-27-2021
    Location
    UK
    MS-Off Ver
    16.51 for Mac
    Posts
    4

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Thank you for that.

    I think I've uploaded a worksheet - as I am using them now - but it's not very clear!

    I've added to the employee tab when the pay rates change into cell notes and what they are changing to is noted at the end of the table for the two 'example' employees. Anything needs clarifying just let me know!

    Thanks again for any help!
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,935

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Are you using Excel for Mac? If so, please add 'for Mac' to your profile.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator 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
    28,420

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Thanks

    You show 3 pay rates for Employee 1

    Nowhere does it say what date the pay rates change
    Your formula in D6 of the Summary is =(512.52+47.4+11.78)/41.5 to give some overall rate of £13.78

    However it's not clear where the three amounts in the parentheses come from nor why you divide by 41.5. Please explain these numbers

    Please explain how you calculate this stuff. If an employee gets paid a different rate at some future point in time then as I said earlier the system needs to know the date the rate changes

  6. #6
    Registered User
    Join Date
    07-27-2021
    Location
    UK
    MS-Off Ver
    16.51 for Mac
    Posts
    4

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Thanks for getting back to me.

    I haven't been given the new pay rate etc yet so this is just an example of what I can expect.

    Like I said in previous post, I have outline the dates the pay rates change in the cell notes in the employee tab (I'll give you the cells the notes are in: AC6 and BG6 for employee one and BU7 for employee 2).

    Not actually sure where that formula in Summary tab D6 came from. At this point how pay rates etc are calculated isn't really where I'm needing help - its more in finding a way of setting out costs for weekly hours while pay rates are constantly changing for each employee at different times!

    For example, at the moment this test sheet is only showing the total cost of hours worked for two employees at one pay rate each. Chances are as time goes on, there is going to be multiple pay rates for everyone.

    Hope that makes sense!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,588

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Although I don't completely understand, perhaps the following (based on Richard's suggestion in post #2) will help.
    On the Employee sheet a table is added that shows the pay rate date ranges.
    Name, Pay Rate and Start Date are manually filled.
    End Date is populated using: =IF(B23=B22,D23,TODAY())
    Hours are populated using: =SUMPRODUCT((A$6:A$16=B22)*(B$5:CF$5>=D22)*(B$5:CF$5<E22)*(B$6:CF$16))
    Pay is populated using: =C22*F22
    On the Summary sheet, the Values column is populated using: =SUMIFS(Employee!G$22:G$26,Employee!B$22:B$26,A6)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    07-27-2021
    Location
    UK
    MS-Off Ver
    16.51 for Mac
    Posts
    4

    Re: Differing pay rates for multiple employees in a job costing spreadsheet?

    Thank you for this. It is along the lines of what I was thinking, so it's good to know I'm on the right track (even if I have very little experience in all this!)

    Going to give that a go, I think I'll only really know what's working when I try it out in practice.

+ 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. Replies: 2
    Last Post: 08-09-2019, 06:23 PM
  2. [SOLVED] Calculating Investment Returns Based On Time and Differing Interest Rates
    By EdwardSnowden in forum Excel General
    Replies: 11
    Last Post: 05-07-2019, 02:39 PM
  3. Replies: 4
    Last Post: 06-30-2016, 08:11 PM
  4. [SOLVED] Help with creating excel spreadsheet for calculating mileage with multiple rates
    By Annahstar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-29-2016, 01:18 PM
  5. [SOLVED] Calculating time/overtime/rates for employees - sheet attached
    By dawsonsoo in forum Excel General
    Replies: 4
    Last Post: 09-30-2014, 12:22 PM
  6. Replies: 2
    Last Post: 03-11-2013, 09:59 PM
  7. [SOLVED] Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]
    By itsavvy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2013, 08:48 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