formula to prorate salary straddling two pay periods

[Manuel998]

hi,

I was wondering if some one could help me with a formulae in cell H&I to prorate the pay based on the pay period and (net work days in column D), I have got a formula in there already but it would be helpful to see if this could be simplified even more. Thanks in advance for any help that you may be able to help with.

Thanks

 

[Hui]

in the Data (Clean) worksheet

H2: =MAX((MIN($G2,Sheet2!B$3)-MAX($F2,Sheet2!B$2))+1,0)*$E2/($G2-$F2)
I2: =MAX((MIN($G2,Sheet2!C$3)-MAX($F2,Sheet2!C$2))+1,0)*$E2/($G2-$F2)

Copy both down

I have assumed that the Cost Column E is fully spread between the Start/End dates

Please check this to ensure your happy with the result

 

[Manuel998]

Hi Hui,
Thanks for the reply, I tried the formulae and it seems to add extra costs eg if you look at saskia the dates perfectly align wit the pay period so there is no need to prorate however with your formula it adds an extra cost to the original cost. hope I am making sense? thanks in advance for any help.

 

[Manuel998]

any ideas if the formula above could be tweaked only to take into account the net working days? thanks Hui

 

[xlstime]

Hi Manuel,

Please replace denominator with NETWORKDAYS($G2,$F2) or use NETWORKDAYS.INTL function if any specif days you want to consider

any ideas if the formula above could be tweaked only to take into account the net working days? thanks Hui

 

[Manuel998]

I don't think the above works for me XLStime maybe I think the intial formula I had is correct I will wait for hui to reply if he can find anything better as the nested if statement are too long for others to follow.

 

[Hui]

Whoops:

H2: =MAX((MIN($G2,Sheet2!B$3)-MAX($F2,Sheet2!B$2)),0)*$E2/($G2-$F2)
I2: =MAX((MIN($G2,Sheet2!C$3)-MAX($F2,Sheet2!C$2)),0)*$E2/($G2-$F2)

Copy both down

 

[Hui]

For Holidays, Can you please post a sample file with a list of holiday dates please