Discussion Re: Calculate partial month salary in Excel
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564395#M152845
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?Sun, 03 Jul 2022 16:47:25 GMTegspen22022-07-03T16:47:25ZCalculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564342#M152820
<P>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. </P><P> </P><P>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.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="egspen2_0-1656855374261.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/385321i3B4B8E301DB86E79/image-size/medium?v=v2&px=400" role="button" title="egspen2_0-1656855374261.png" alt="egspen2_0-1656855374261.png" /></span></P><P> </P>Sun, 03 Jul 2022 13:37:04 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564342#M152820egspen22022-07-03T13:37:04ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564352#M152826
<P><LI-USER uid="827443"></LI-USER> 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``</P><P> </P><P>Are you sure about that?</P><P> </P><P>Most employees do not work every day of the month, but they are still given full-month credit.</P><P> </P><P>Or is this a class exercise, and the rules do not have to make sense?</P><P> </P><P>Even so, I wonder if the exercise expects you to prorate based on NETWORKDAYS, at the very least.</P><P> </P>Sun, 03 Jul 2022 15:31:22 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564352#M152826Joe User2022-07-03T15:31:22ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564354#M152827
<P><LI-USER uid="827443"></LI-USER> </P>
<P> </P>
<P>=IF($H28="",0,MAX(MIN($H28,N$10)-MAX($G28,EOMONTH(N$10,-1))+1,0)/N$9*$M4)</P>Sun, 03 Jul 2022 14:35:25 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564354#M152827Hans Vogelaar2022-07-03T14:35:25ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564395#M152845
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?Sun, 03 Jul 2022 16:47:25 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564395#M152845egspen22022-07-03T16:47:25ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564400#M152846
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>Thank you! A few questions:</P><P>1) can you confirm that "$M4" at the end of your example should actually be "$M28"?<BR />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?</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="egspen2_0-1656867706991.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/385330i865C8C159356DE6C/image-size/medium?v=v2&px=400" role="button" title="egspen2_0-1656867706991.png" alt="egspen2_0-1656867706991.png" /></span></P><P> </P><P> </P>Sun, 03 Jul 2022 17:02:03 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564400#M152846egspen22022-07-03T17:02:03ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564422#M152849
<P><LI-USER uid="827443"></LI-USER> </P>
<P>1) Yes - $M4 was a typo.</P>
<P>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.</P>Sun, 03 Jul 2022 18:11:34 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564422#M152849Hans Vogelaar2022-07-03T18:11:34ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564436#M152855
<P><LI-USER uid="827443"></LI-USER> wrote: ``even though I don't actually work 30 or 31 days in a month, I'm paid for that amount of days``</P><P> </P><P>I think you mean: you should be paid for <U><EM>the entire month</EM></U>.</P><P> </P><P>And that is <U><EM>exactly my point</EM></U>: if you are salaried monthly (or any multiple) and you terminate employment on the last <U><EM>workday</EM></U> of the month, you should be paid an <U><EM>entire month's</EM></U> salary.</P><P> </P><P>For example, if the last <U><EM>workday</EM></U> 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 <U><EM>entire month</EM></U>.</P><P> </P><P>But I think the algorithm that you described and that HansV implemented would pay only <U><EM>26/30 of a month's</EM></U> salary.</P><P> </P><P>-----</P><P> </P><P>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 <U><EM>not necessarily</EM></U> on the number of <U><EM>workdays</EM></U> in the month (ostensibly what the Excel function NETWORKDAYS can return).</P><P> </P><P>For example, at the company that I worked for (for 37 years!), if a monthly-salaried employee starts on the last <U><EM>workday</EM></U> on or before the 15th of a month, he/she is paid for the entire month.</P><P> </P><P>And if a monthly-salaried employee terminates on the first <EM><U>workday</U></EM> on or after the 15th of a month, he/she is paid for the entire month.</P><P> </P><P>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 <U><EM>workdays</EM></U> in the month (ostensibly NETWORKDAYS). For my Nov example above, that would <U><EM>salary divided by 26</EM></U>, not 30, per <U><EM>workday</EM></U>.</P><P> </P><P>-----</P><P> </P><P>But I digress and perhaps over-complicate things to help you to better understand the point of my original question.</P><P> </P><P>I raised the question merely to ensure that you did your due diligence about what rules should apply.</P><P> </P><P>If this is a class exercise, follow its rules.</P><P> </P><P>If this is a real-life problem and you fully understand the rules, nothing that I wrote matters.</P><P> </P><P>And If now you have some doubts, again nothing that I wrote matters.</P><P> </P><P>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.</P><P> </P><P>Good luck!</P>Sun, 03 Jul 2022 20:38:14 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3564436#M152855Joe User2022-07-03T20:38:14ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3565576#M153021
<P><LI-USER uid="127945"></LI-USER> </P><P> </P><P>Sample attached with 3 different scenarios (employees).</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P>Tue, 05 Jul 2022 10:55:02 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3565576#M153021egspen22022-07-05T10:55:02ZRe: Calculate partial month salary
https://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3565591#M153022
<P><LI-USER uid="827443"></LI-USER> </P>
<P>Thank. See the attached version.</P>Tue, 05 Jul 2022 11:12:59 GMThttps://techcommunity.microsoft.com/t5/excel/calculate-partial-month-salary/m-p/3565591#M153022Hans Vogelaar2022-07-05T11:12:59Z