Hello everyone!
I currently have this spreadsheet in progress that will be used as a template to decipher a repayment period. Users will enter a start date into the 'Payslip Start Date' and 'Repayment Start Date', then enter the hours from a payslip and we have to calculate the total for each week.
I am having a lot of trouble finding the right formula to use that will correctly count the individual days of the 'Payslip Start' and 'Payslip End' dates that fit within the 'Payment Start' and 'Payment End' date period (see B9:B34-C9:C34 and J9:J34-K9:K34).
As you can see from the attached, in column L, I was playing around with a MIN/Max formula, however it will only work correctly if the dates exactly match in both sets of columns (see below). In this case, the '14' in column L is correct as there are the 14 days from the 01/01/2022 - 14/01/2022 (in B and C) that align with the date ranges in J and K.
However, if I was to change the Payslip Start to the 05/01/2022, it will only count '10 days' but continues to repeat that. I understand that it will always read the same as there are only ever 10 numbers in the row that align, however I am hoping that will be able to recognise somehow that the row beneath it contains some of the date range as well. If the formula works, it should hopefully show (or something like it). i.e.
Row 1: 05/01/2022 - 18/01/2022 has 10 days that fit within the 01/01/2022 - 14/01/2022 but the remaining 4 days fit into row 2 (15/01/2022 - 28/01/2022).
Counting the individual days will be important as if a payment period end durings the middle of the week (for example), we would then need to work out the daily rate of hours and times that by the number of days actually completed in the payment period. I am thinking that I will need to extend the columns in J and K to include all of the possibles date combinations, but I thought I would ask if it could be done first?
Thank you all so much!
I currently have this spreadsheet in progress that will be used as a template to decipher a repayment period. Users will enter a start date into the 'Payslip Start Date' and 'Repayment Start Date', then enter the hours from a payslip and we have to calculate the total for each week.
I am having a lot of trouble finding the right formula to use that will correctly count the individual days of the 'Payslip Start' and 'Payslip End' dates that fit within the 'Payment Start' and 'Payment End' date period (see B9:B34-C9:C34 and J9:J34-K9:K34).
As you can see from the attached, in column L, I was playing around with a MIN/Max formula, however it will only work correctly if the dates exactly match in both sets of columns (see below). In this case, the '14' in column L is correct as there are the 14 days from the 01/01/2022 - 14/01/2022 (in B and C) that align with the date ranges in J and K.
| Book2.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Payslip Periods | Fortnightly | 14 | |||||||||||||
| 2 | Payment Frequency | Fortnightly | ||||||||||||||
| 3 | Payment Start Date | 1/01/2022 | ||||||||||||||
| 4 | Payment End Date | 2/07/2022 | ||||||||||||||
| 5 | Total Payable | |||||||||||||||
| 6 | Payslip Start Date | 1/01/2022 | ||||||||||||||
| 7 | ||||||||||||||||
| 8 | Payslip Start | Payslip End | Total Hours | Daily Rate HRS | Cumulative HRS in Payslips | Cumulative Average Hours per Payment Due Week | Payment Period Start | Payment Period End | ||||||||
| 9 | 1/01/2022 | 14/01/2022 | 20 | 1.43 | 14.00 | 20.00 | 20.00 | 1/01/2022 | 14/01/2022 | 14 | 0 | 0 | ||||
| 10 | 15/01/2022 | 28/01/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 15/01/2022 | 28/01/2022 | 14 | 0 | 0 | ||||
| 11 | 29/01/2022 | 11/02/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 29/01/2022 | 11/02/2022 | 14 | 0 | 0 | ||||
| 12 | 12/02/2022 | 25/02/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 12/02/2022 | 25/02/2022 | 14 | 0 | 0 | ||||
| 13 | 26/02/2022 | 11/03/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 26/02/2022 | 11/03/2022 | 14 | 0 | 0 | ||||
| 14 | 12/03/2022 | 25/03/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 12/03/2022 | 25/03/2022 | 14 | 0 | 0 | ||||
| 15 | 26/03/2022 | 8/04/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 26/03/2022 | 8/04/2022 | 14 | 0 | 0 | ||||
| 16 | 9/04/2022 | 22/04/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 9/04/2022 | 22/04/2022 | 14 | 0 | 0 | ||||
| 17 | 23/04/2022 | 6/05/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 23/04/2022 | 6/05/2022 | 14 | 0 | 0 | ||||
| 18 | 7/05/2022 | 20/05/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 7/05/2022 | 20/05/2022 | 14 | 0 | 0 | ||||
| 19 | 21/05/2022 | 3/06/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 21/05/2022 | 3/06/2022 | 14 | 0 | 0 | ||||
| 20 | 4/06/2022 | 17/06/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 4/06/2022 | 17/06/2022 | 14 | 0 | |||||
| 21 | 18/06/2022 | 1/07/2022 | 20 | 1.43 | 0.00 | 20.00 | 20.00 | 18/06/2022 | 1/07/2022 | 14 | ||||||
| 22 | 0.00 | 0.00 | 0.00 | 18.57 | ||||||||||||
| 23 | 0.00 | 0.00 | 0.00 | 17.33 | ||||||||||||
| 24 | 0.00 | 0.00 | 0.00 | 16.25 | ||||||||||||
| 25 | 0.00 | 0.00 | 0.00 | 15.29 | ||||||||||||
| 26 | 0.00 | 0.00 | 0.00 | 14.44 | ||||||||||||
| 27 | 0.00 | 0.00 | 0.00 | 13.68 | ||||||||||||
| 28 | 0.00 | 0.00 | 0.00 | 13.00 | ||||||||||||
| 29 | 0.00 | 0.00 | 0.00 | 12.38 | ||||||||||||
| 30 | 0.00 | 0.00 | 0.00 | 11.82 | ||||||||||||
| 31 | 0.00 | 0.00 | 0.00 | 11.30 | ||||||||||||
| 32 | 0.00 | 0.00 | 0.00 | 10.83 | ||||||||||||
| 33 | 0.00 | 0.00 | 0.00 | 10.40 | ||||||||||||
| 34 | 0.00 | 0.00 | 0.00 | 10.00 | ||||||||||||
Sheet1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D1 | D1 | =IFS(B1="Weekly","7",B1="Fortnightly","14",B1="Monthly","30.31") |
| B4 | B4 | =($B$3+182) |
| B9:B21 | B9 | =LET(p,MATCH(B1,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B6,CHOOSE(p,7,14,30.41))) |
| C9:C21 | C9 | =B9#+IF(B1="Weekly",6,IF(B1="Monthly",29.41,13)) |
| E9:E34 | E9 | =D9/$D$1 |
| F9:F34 | F9 | =LET(Y,SEQUENCE(K9-J9+1),X,TRANSPOSE(J9+Y-1),MMULT((X>=B9:B34)*(X<=C9:C34),Y^0)) |
| G9:G34 | G9 | =SUMPRODUCT(($B$9:$B$34>=J9)*($C$9:$C$34<=K9)*$D$9:$D$34) |
| H9 | H9 | =D9/1 |
| H10 | H10 | =SUM(D9:D10)/2 |
| H11 | H11 | =SUM(D9:D11)/3 |
| H12 | H12 | =SUM(D9:D12)/4 |
| H13 | H13 | =SUM(D9:D13)/5 |
| H14 | H14 | =SUM(D9:D14)/6 |
| H15 | H15 | =SUM(D9:D15)/7 |
| H16 | H16 | =SUM(D9:D16)/8 |
| H17 | H17 | =SUM(D9:D17)/9 |
| H18 | H18 | =SUM(D9:D18)/10 |
| H19 | H19 | =SUM(D9:D19)/11 |
| H20 | H20 | =SUM(D9:D20)/12 |
| H21 | H21 | =SUM(D9:D21)/13 |
| H22 | H22 | =SUM(D9:D22)/14 |
| H23 | H23 | =SUM(D9:D23)/15 |
| H24 | H24 | =SUM(D9:D24)/16 |
| H25 | H25 | =SUM(D9:D25)/17 |
| H26 | H26 | =SUM(D9:D26)/18 |
| H27 | H27 | =SUM(D9:D27)/19 |
| H28 | H28 | =SUM(D9:D28)/20 |
| H29 | H29 | =SUM(D9:D29)/21 |
| H30 | H30 | =SUM(D9:D30)/22 |
| H31 | H31 | =SUM(D9:D31)/23 |
| H32 | H32 | =SUM(D9:D32)/24 |
| H33 | H33 | =SUM(D9:D33)/25 |
| H34 | H34 | =SUM(D9:D34)/26 |
| J9:J21 | J9 | =LET(p,MATCH(B2,{"Fortnightly","Monthly","Quarterly","Milestone","Lump Sum"},0),SEQUENCE(CHOOSE(p,13,6,2,2,1),,B3,CHOOSE(p,14,30.41,91,91,182))) |
| K9:K21 | K9 | =J9#+IFS(B2="Fortnightly",13,B2="Monthly",29.41,B2="Quarterly",90,B2="Quarterly",90,B2="Milestone",90,B2="Lump Sum",181) |
| L9:L21 | L9 | =IF($B9<>"",TRANSPOSE(MAX(MIN($K9,$C9)-MAX($J9,$B9)+1,0)),"") |
| M9:M21 | M9 | =IF($B10<>"",TRANSPOSE(MAX(MIN($K9,$C10)-MAX($J9,$B10)+1,0)),"") |
| N9:N21 | N9 | =IF($B11<>"",TRANSPOSE(MAX(MIN($K9,$C11)-MAX($J9,$B11)+1,0)),"") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Dynamic array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| H9:H34 | Cell Value | >=20 | text | NO |
However, if I was to change the Payslip Start to the 05/01/2022, it will only count '10 days' but continues to repeat that. I understand that it will always read the same as there are only ever 10 numbers in the row that align, however I am hoping that will be able to recognise somehow that the row beneath it contains some of the date range as well. If the formula works, it should hopefully show (or something like it). i.e.
Row 1: 05/01/2022 - 18/01/2022 has 10 days that fit within the 01/01/2022 - 14/01/2022 but the remaining 4 days fit into row 2 (15/01/2022 - 28/01/2022).
| Payslip Start | Payslip End | Payment Period Start | Payment Period End | ||
5/01/2022 | 18/01/2022 | 01/01/2022 | 14/01/2022 | 10 | |
19/01/2022 | 1/02/2022 | 15/01/2022 | 28/01/2022 | 4 (the remaining 4 days in row 1 fit into this payment period) | |
2/02/2022 | 15/02/2022 | 29/01/2022 | 11/02/2022 | ||
16/02/2022 | 1/03/2022 | 12/02/2022 | 25/02/2022 | ||
2/03/2022 | 15/03/2022 | 26/02/2022 | 11/03/2022 | ||
16/03/2022 | 29/03/2022 | 12/03/2022 | 25/03/2022 | ||
30/03/2022 | 12/04/2022 | 26/03/2022 | 08/04/2022 | ||
13/04/2022 | 26/04/2022 | 09/04/2022 | 22/04/2022 | ||
27/04/2022 | 10/05/2022 | 23/04/2022 | 06/05/2022 | ||
11/05/2022 | 24/05/2022 | 07/05/2022 | 20/05/2022 | ||
25/05/2022 | 7/06/2022 | 21/05/2022 | 03/06/2022 | ||
8/06/2022 | 21/06/2022 | 04/06/2022 | 17/06/2022 | ||
22/06/2022 | 5/07/2022 | 18/06/2022 | 01/07/2022 |
Counting the individual days will be important as if a payment period end durings the middle of the week (for example), we would then need to work out the daily rate of hours and times that by the number of days actually completed in the payment period. I am thinking that I will need to extend the columns in J and K to include all of the possibles date combinations, but I thought I would ask if it could be done first?
Thank you all so much!
