Calculate partial month salary

[egspen2]

I am trying to figure out the best formula that will calculate either a) a full month of salary or b) a partial month salary if the start or end date indicate an employee was not there for the full month. 

For example, in the screenshot below, the employee on the second line should only get 23/31 days of salary in July 22 (dates on the top row are EOMONTH) and no salary after July 22.

[#2]

@egspen2  wrote:  ``a partial month salary if the start or end date indicate an employee was not there for the full month [....] should only get 23/31 days of salary``

Are you sure about that?

Most employees do not work every day of the month, but they are still given full-month credit.

Or is this a class exercise, and the rules do not have to make sense?

Even so, I wonder if the exercise expects you to prorate based on NETWORKDAYS, at the very least.

[#3]

@egspen2 

=IF($H28="",0,MAX(MIN($H28,N$10)-MAX($G28,EOMONTH(N$10,-1))+1,0)/N$9*$M4)

[#4]

Fair question but in this instance I'm calculating for salaried employees, not hourly. So, even though I don't actually work 30 or 31 days in a month, I'm paid for that amount of days. Is your comment re: "NETWORKDAYS" speaking to an Excel function?

[#5]

@Hans Vogelaar 

Thank you! A few questions:

1) can you confirm that "$M4" at the end of your example should actually be "$M28"?
2) When I copy that formula to an example of a person whose end date is in the next year (2023 in this case), the only 2022 month that's showing up properly is the first month. See below where after July, the monthly amounts being calculated are greater than the 2022 Monthly salary. What part of the formula is causing this?

[#6]

@egspen2 

1) Yes - $M4 was a typo.

2) Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#7]

@egspen2  wrote:  ``even though I don't actually work 30 or 31 days in a month, I'm paid for that amount of days``

I think you mean:  you should be paid for the entire month.

And that is exactly my point:  if you are salaried monthly (or any multiple) and you terminate employment on the last workday of the month, you should be paid an entire month's salary.

For example, if the last workday of Nov is Wed the 26th because Sun is the 30th and the 27th and 28th are paid company holidays (for Thanksgiving in the US), I believe you should be paid for the entire month.

But I think the algorithm that you described and that HansV implemented would pay only 26/30 of a month's salary.

-----

That said, you are right to a degree:  the proration of partial starting and terminating months very much depends on company policy and applicable labor law, and not necessarily on the number of workdays in the month (ostensibly what the Excel function NETWORKDAYS can return).

For example, at the company that I worked for (for 37 years!), if a monthly-salaried employee starts on the last workday on or before the 15th of a month, he/she is paid for the entire month.

And if a monthly-salaried employee terminates on the first workday on or after the 15th of a month, he/she is paid for the entire month.

In the rare instance where a salaried employee cannot abide by those limitations, the month's pay was determined by prorating the monthly salary by the number of workdays in the month (ostensibly NETWORKDAYS).  For my Nov example above, that would salary divided by 26, not 30, per workday.

-----

But I digress and perhaps over-complicate things to help you to better understand the point of my original question.

I raised the question merely to ensure that you did your due diligence about what rules should apply.

If this is a class exercise, follow its rules.

If this is a real-life problem and you fully understand the rules, nothing that I wrote matters.

And If now you have some doubts, again nothing that I wrote matters.

But hopefully it encourages you to ask the right questions of the right people (or labor laws) to determine the rules that you should apply.

Good luck!

[#8]

@Hans Vogelaar 

Sample attached with 3 different scenarios (employees).

First is an employee who should have the same value in every month ($13,750) as they are expected to be here July through December.

Second is an employee leaving in July, mid-month so should have less than the monthly amount ($9,785) in July and $0 for each remaining month.

Third is an employee that should have partial salary in July as they are starting mid-month but should have the same full month for each month of Aug - December.

[#9]

@egspen2 

Thank. See the attached version.

[#10]

Calculating a partial month's salary can be a pain, but it's essential to get it right. In your case, you could use a formula that calculates the number of days the employee worked in the month and then divides that by the total number of days in the month.