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
[TABLE="width: 391"]
<tbody>[TR]
[TD]Date[/TD]
[TD]CalMonthNumber[/TD]
[TD] CalMonth[/TD]
[TD] Fin Period[/TD]
[/TR]
[TR]
[TD="align: right"]27/12/2017[/TD]
[TD] 12[/TD]
[TD] Dec[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD="align: right"]28/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD="align: right"]29/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD="align: right"]30/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD] 1[/TD]
[TD]Jan[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2018[/TD]
[TD] 1[/TD]
[TD]Jan[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2018[/TD]
[TD] 1[/TD]
[TD]Jan[/TD]
[TD] 1[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 391"]
<tbody>[TR="bgcolor: transparent"]
[TD]Date[/TD]
[TD]CalMonthNumber[/TD]
[TD] CalMonth[/TD]
[TD] Fin Period[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]27/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"] Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]28/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]29/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]30/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]31/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]01/01/2018[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]02/01/2018[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]03/01/2018[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 391"]
<tbody>[TR]
[TD]Date[/TD]
[TD]CalMonthNumber[/TD]
[TD] CalMonth[/TD]
[TD] Fin Period[/TD]
[/TR]
[TR]
[TD="align: right"]27/12/2017[/TD]
[TD] 12[/TD]
[TD] Dec[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD="align: right"]28/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD="align: right"]29/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 12[/TD]
[/TR]
[TR]
[TD="align: right"]30/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2017[/TD]
[TD] 12[/TD]
[TD]Dec[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD] 1[/TD]
[TD]Jan[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2018[/TD]
[TD] 1[/TD]
[TD]Jan[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2018[/TD]
[TD] 1[/TD]
[TD]Jan[/TD]
[TD] 1[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 391"]
<tbody>[TR="bgcolor: transparent"]
[TD]Date[/TD]
[TD]CalMonthNumber[/TD]
[TD] CalMonth[/TD]
[TD] Fin Period[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]27/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"] Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]28/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]29/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]30/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]31/12/2017[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent"] 12[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]01/01/2018[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]02/01/2018[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD="align: right"]03/01/2018[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent"] 1[/TD]
[/TR]
</tbody>[/TABLE]