Monthly wages calculation
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 | | |
|---|
|
|---|
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]
