Formula to prorate monthly salaries based on start and end date

[IPOK]

I currently have a formula below that works for calculating the monthly salary based on start and end date. Here it is....

IF(OR($B2>EOMONTH(E$1,0),$C2<E$1),"",$D2*(MIN(EOMONTH(E$1,0),$C2)-MAX(E$1,$B2)+1)/IF(MOD(YEAR($E$1),4)=0,366,365))


This calculates the monthly salary based on the number of days in the month compared to the annual salary. As a result, each monthly salary is different based on the number of days.

I would like to change this formula so that the monthly salary is the same each month except for partial months (either the start date month or the end date month). In this case, the prorated salary for the month that has a partial date should calculate based on the # of days in month as a % of total days in that month.

For example, a person with annual salary of $120,000 has a monthly salary of $10,000. If the person is starting on May 15, 2024, the salary cost for the month of May is $5,484. From May 15th through May 31st is 17 days, so the cost is equal to 17*(10,000/31). I would then want to see a monthly salary cost of $10,000 from June onwards until the person leaves. The month they leave would be prorated similarly.

Can anyone help with this update to the above formula? Thanks!

[DJunqueira]

Please help us to help you and attach a worksheet.

[IPOK]

I'm new here and don't know how to add a file. I'll look into it and try to add file tomorrow. thanks.

[avk]

Refer yellow banner on top of screen.
HOW TO ATTACH YOUR SAMPLE WORKBOOK:Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[IPOK]

I have added a file showing the current working formula based on number of days (Row 2) and the desired result (Row 5) which would calculate based on equal monthly cost.

Thanks!

[josephteh]

Try in E2, =MAX(MIN($C2,EOMONTH(E$1,0))-MAX($B2,E$1)+1,0)/DAY(EOMONTH(E$1,0))*$D2/12, copy across and down.

[IPOK]

Thank you so much. This worked perfectly!

[josephteh]

You are welcome, glad to help, thanks for the Rep!