Estimating staff costs based on start and end dates, by month

[michaelr96]

Hello all,

I am trying to create a staffing budget that will pro-rate their gross salary into each pay period, taking into account any contract changes i.e. a member of staff may reduce hours in year, and I would like to create a template that would allow for the new contract to be entered, and it would then estimate costs based on the dates entered.

Below is the information I am currently working off of, but the closest I can get is for the formula to do whole month calculations, rather than based on relevant days in the period for each month. I am unable to produce accurate results if the end dates are not the end of the month (which would be unlikely in reality)

Payroll Number

Name

Job Title

Start Date

End Date

No of Days

No of Months

Salary

Aug-20​

Sep-20​

Oct-20​

Nov-20​

Dec-20​

Jan-21​

Feb-21​

Mar-21​

Apr-21​

May-21​

Jun-21​

Jul-21​

1234567​

John Smith

Teacher

01/08/2020​

31/08/2020​

30.00​

01

£ 20,000.00

£ 1,612.90

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

1234567​

John Smith

Head of Department

01/09/2020​

31/03/2021​

211.00​

07

£ 25,000.00

FALSE​

£ 2,025.73

£ 2,025.73

£ 2,025.73

£ 2,025.73

£ 2,025.73

£ 2,025.73

£ 2,025.73

FALSE​

FALSE​

FALSE​

FALSE​

1234567​

John Smith

Head of Department

01/04/2021​

31/05/2021​

60.00​

02

£ 40,000.00

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

£ 3,225.81

£ 3,225.81

FALSE​

FALSE​

1234567​

John Smith

Teacher

01/06/2021​

31/07/2021​

60.00​

02

£ 20,000.00

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

FALSE​

£ 1,612.90

£ 1,612.90

Any pointers would be greatly appreciated!

Thanks,
Michael

 

[DRSteele]

Welcome to the forum.

How did you calculate 1612.90?
How did you determine No of Months and what is it used for? If the dates are mid-month, how many months would they be?

 

[michaelr96]

Welcome to the forum.

How did you calculate 1612.90?
How did you determine No of Months and what is it used for? If the dates are mid-month, how many months would they be?

Hello,
This was calculated by using this "=IF(AND(S$1>=$D5,S$1<=$E5),($H5/(31*12))*($F5/$G5))"

Thanks,
Michael

 

[michaelr96]

Welcome to the forum.

How did you calculate 1612.90?
How did you determine No of Months and what is it used for? If the dates are mid-month, how many months would they be?

Only just saw the reference to the number of months, this is a field currently used to split payment over the relevant period, but ultimately the formula would purely look at the start and end dates on each row, and pro rate into each month

Michael