I was previously helped with calculating a period number that changed on the last Friday of the month unless the last calendar day fell on a Monday or Tuesday in which case it was the next Friday
This works perfectly:
=MOD(MONTH($A224)-1+AND(DAY($A224)>27,WEEKDAY($A224,13)>3,DAY($A224)-WEEKDAY($A224,13)>23)-AND(DAY($A224)<3,WEEKDAY($A224,14)<3,DAY($A224)<=WEEKDAY($A224,14)),12)+1
The above formula gives me the following results
<tbody>
</tbody>
How can I alter this to take account of the fact that Period 1 will always start on 1st January and period 12 will always end on 31st Dec
Desired results
<tbody>
</tbody>
This works perfectly:
=MOD(MONTH($A224)-1+AND(DAY($A224)>27,WEEKDAY($A224,13)>3,DAY($A224)-WEEKDAY($A224,13)>23)-AND(DAY($A224)<3,WEEKDAY($A224,14)<3,DAY($A224)<=WEEKDAY($A224,14)),12)+1
The above formula gives me the following results
Date | CalMonthNumber | CalMonth | Fin Period |
27/12/2017 | 12 | Dec | 12 |
28/12/2017 | 12 | Dec | 12 |
29/12/2017 | 12 | Dec | 12 |
30/12/2017 | 12 | Dec | 1 |
31/12/2017 | 12 | Dec | 1 |
01/01/2018 | 1 | Jan | 1 |
02/01/2018 | 1 | Jan | 1 |
03/01/2018 | 1 | Jan | 1 |
<tbody>
</tbody>
How can I alter this to take account of the fact that Period 1 will always start on 1st January and period 12 will always end on 31st Dec
Desired results
Date | CalMonthNumber | CalMonth | Fin Period |
27/12/2017 | 12 | Dec | 12 |
28/12/2017 | 12 | Dec | 12 |
29/12/2017 | 12 | Dec | 12 |
30/12/2017 | 12 | Dec | 12 |
31/12/2017 | 12 | Dec | 12 |
01/01/2018 | 1 | Jan | 1 |
02/01/2018 | 1 | Jan | 1 |
03/01/2018 | 1 | Jan | 1 |
<tbody>
</tbody>