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

#### michaelr96

##### New Member
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### DRSteele

##### Well-known Member
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?

Last edited:

#### michaelr96

##### New Member
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

##### New Member
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

Replies
3
Views
142
Replies
3
Views
21
Replies
3
Views
48
Replies
3
Views
352
Replies
21
Views
907