Forum Discussion

egspen2's avatar
egspen2
Copper Contributor
Jul 03, 2022

Calculate partial month salary

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 m...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Jul 03, 2022

egspen2 

 

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

egspen2's avatar
egspen2
Copper Contributor
Jul 03, 2022

HansVogelaar 

 

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?