Monthly wages calculation

[KJG03]

I have a curly one. I need to calculate each months wages for an individual staff member (row 8) based on start and/or finish dates or, if blank, start & end of financial year (J8 & K8). Also based on total in columns Z and AA depending on which month is being calculated. Also, to make it even trickier, to calculate this amount based on the number of pay fortnights in each month as displayed in row 3 of each month.

Book1
	J	K	Z	AA	AB	AC
1			01/07/2018	01/12/2018
2			30/11/2018	30/06/2019	01/07/2018	01/08/2018
3			#days	#days	2	2
4	Start Date	Finish Date	153	212	31	31
5	Start Date	Finish Date	Jul-Nov TOTAL incl. Outsourced Wages BUT excl. on-costs & wage subsidy	Dec-Jun TOTAL incl. Outsourced Wages BUT excl. on-costs & wage subsidy	31/07/2018	31/08/2018
6	01/10/2018		$ 13,401.83	$ 19,126.95
Wages 2018-19

Cell Formulas
Range	Formula
Z4	=Z2-Z1+1
Z6	=SUM(($P6+$X6)/365*$Z$4)
AA4	=AA2-AA1+1
AA6	=SUM((P6+X6)/365*$AA$4)+R6
AB4	=+AB5-AB2+1
AC4	=+AC5-AC2+1

 

[KJG03]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

J​

[/th][th]

K​

[/th][th]

Z​

[/th][th]

AA​

[/th][th]

AB​

[/th][th]

AC​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#F2F2F2]

01/07/2018​

[/td][td=bgcolor:#F2F2F2]

01/12/2018​

[/td][td=bgcolor:#FFEB9C][/td][td=bgcolor:#FFEB9C][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#F2F2F2]

30/11/2018​

[/td][td=bgcolor:#F2F2F2]

30/06/2019​

[/td][td=bgcolor:#F2F2F2]

01/07/2018​

[/td][td=bgcolor:#F2F2F2]

01/08/2018​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#F2F2F2]

#days​

[/td][td=bgcolor:#F2F2F2]

#days​

[/td][td=bgcolor:#F2F2F2]

2​

[/td][td=bgcolor:#F2F2F2]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#FFFFFF][/td][td=bgcolor:#F2F2F2]

153​

[/td][td=bgcolor:#F2F2F2]

212​

[/td][td=bgcolor:#F2F2F2]

31 ​

[/td][td=bgcolor:#F2F2F2]

31 ​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#FFFFCC]Start Date[/td][td=bgcolor:#FFFFCC]Finish Date[/td][td=bgcolor:#FFFFCC] Jul-Nov TOTAL incl. Outsourced Wages BUT excl. on-costs & wage subsidy [/td][td=bgcolor:#FFFFCC] Dec-Jun TOTAL incl. Outsourced Wages BUT excl. on-costs & wage subsidy [/td][td=bgcolor:#FFFFCC]

31/07/2018​

[/td][td=bgcolor:#FFFFCC]

31/08/2018​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#70AD47]

01/10/2018​

[/td][td][/td][td]

$ 13,401.83 ​

[/td][td]

$ 19,126.95 ​

[/td][td=bgcolor:#D9D9D9][/td][td=bgcolor:#D9D9D9][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Wages 2018-19[/td][/tr][/table]