+ Reply to Thread
Results 1 to 3 of 3

Monthly cost in wages based on employee start/end dates (diferent departments)

  1. #1
    Registered User
    Join Date
    03-26-2016
    Location
    Akron, Ohio
    MS-Off Ver
    2007
    Posts
    1

    Monthly cost in wages based on employee start/end dates (diferent departments)

    Hello!

    I am looking for help creating a forumla that can help calculate monthly wage expenses for multiple employees among multiple departments (assuming a 40 hour work week), including different start and end dates throughout the month.

    Fictional example: We have 72 employees that worked during the month of February 2015. Some worked the whole month. Others were hired in in the middle of the month, others may have left the business during the last week of the month. They also work in different departments with different hourly wages.

    Is there a formula that can go through an employee roster spreadsheet that includes start/end dates as well as departments and wages, and give a total sum for the wages paid for a specific month (example, 2/1/15 though 2/28/15)?

    I consider myself somewhere in the intermediate level of Excel. My gut tells me it's an incredibly complicated forumula that will involve a lot of COUNTIF's, but I don't know if that's right, or if there is an easier way.

    Any information is appreciated. I can also try rewording what I'm looking for if anyone is confused.

    Thanks!

  2. #2
    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
    27,991

    Re: Monthly cost in wages based on employee start/end dates (diferent departments)

    Initial thoughts:

    you require one or more tables defining the wage structure e.g. Department, Staff position, Pay rate (Hourly) .....

    It will help if you can provide an excel file defining your data: try to make this as realistic as possible, including employment dates. We only need about 20 rows of date, ideally with sample calculations/results.

    To upload a file, click "Go Advanced" then "Manage Attachments".

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Re: Monthly cost in wages based on employee start/end dates (diferent departments)

    ctechau,

    Try the attached.

    Cols A and B give the employee name and their Department.

    Col C is the start date in the month, and Col E is the end date.

    Col D allows you to deduct any days taken as holiday in the month.

    COl F calculates the working days between the dates, deducts any holidays you enter, then multiplies the total days by eight to get the hours worked,

    =(NETWORKDAYS(C2,E2)-D2)*8

    Col G is the hourly pay rate.

    K2 then calculates the total wage bill for the month:

    =SUMPRODUCT(F:F,G:G)

    Hope that helps

    Ochimus
    Attached Files Attached Files

+ 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. Which formula for splitting the total cost between departments?
    By grandpacool in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2015, 12:26 PM
  2. Monthly values linked with different start dates
    By Billium in forum Excel General
    Replies: 1
    Last Post: 10-29-2014, 07:04 PM
  3. Replies: 1
    Last Post: 06-10-2014, 10:43 PM
  4. [SOLVED] Split periods between start and end dates and adjust a cost accordingly.
    By paulhoskin in forum Excel General
    Replies: 3
    Last Post: 11-06-2013, 09:01 AM
  5. Employee Tardiness based off start time
    By 180drft in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 03:54 PM
  6. [SOLVED] HELP... Commission of 20% if an employee makes more than 2.7 times their wages
    By pogo.stix in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-08-2012, 09:23 AM
  7. Sum of wages for the year with varying start dates
    By Ainsley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-26-2007, 12:41 PM

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