Prorating Salary based on hire date and Term date - by month

[KH Consulting]

I would like to calculate the prorated employees monthly pay by start and end date in a given month. but show all other month with the standard pay
Assume: $120,000 Annual Pay
Assume: $10,000 Monthly Pay
***Prorate if they leave or start during a month... can be a fraction of that month.... (denominator of # of days in a month OR just a standard of 30 days)

RIght now the formula I am using works, but it calculates based on 365/366 days thus each month the salary goes up and down instead of an even $10,000
Here is the formula I am using:
=IF(OR($BQ3>EOMONTH(BD$1,0),$BU3<BD$1),"",$R3*(MIN(EOMONTH(BD$1,0),$BU3)-MAX(BD$1,$BQ3)+1)/IF(MOD(YEAR(BD$1),4)=0,366,365))

*** bonus if I can have the end date blank and still return a salary... but that isn't necessary

 

[MountainFog]

 

[Tetra201]

Another approach (bonus feature included):

=$R3/12*MAX(0,MIN(EOMONTH(BD$1,0),$BU3)-MAX(BD$1,IF($BQ3="",BD$1+33,$BQ3))+1)/DAY(EOMONTH(BD$1,0))

 

[KH Consulting]

View attachment 109150

Thanks! this formulas worked
=LET(ed,IF($D3="",2958465,$D3),IFS(($C3>EOMONTH(E$1,0))+(ed<E$1),"",($C3>E$1)+(ed<EOMONTH(E$1,0)),$B3/12*((MAX(E$1,ed)-MAX(E$1,$C3))+1)/(EOMONTH(E$1,0)-E$1+1),1,$B3/12))

 

[KH Consulting]

Another approach (bonus feature included):

=$R3/12*MAX(0,MIN(EOMONTH(BD$1,0),$BU3)-MAX(BD$1,IF($BQ3="",BD$1+33,$BQ3))+1)/DAY(EOMONTH(BD$1,0))

Thank you