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

michaelr96

New Member
Joined
May 5, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 NumberNameJob TitleStart DateEnd DateNo of DaysNo of MonthsSalary
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 SmithTeacher
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 SmithHead 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 SmithHead 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 SmithTeacher
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,308
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 5, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
May 5, 2020
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,824
Messages
5,544,539
Members
410,619
Latest member
gregor222
Top