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

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. ## 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.

3. ## 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

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

#### 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