Calculating Finance Period Number when not calendar period number

[AliBb]

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]

 

[StephenCrump]

Your formula doesn't match your description, e.g. the last Friday in January 2018 is the 26th. 31 Jan 2018 is a Sunday, therefore based on your description, the period should change from 1 on Friday 26th to 2 on Saturday 27th?

But using your formula the period doesn't change to 2 until Saturday 3 February.

 

[StephenCrump]

Ha! Wasting my time.

Reposted from here: https://www.mrexcel.com/forum/excel-questions/1054421-financial-period-start-date.html

 

[AliBb]

Thanks but this is a variation on that so wasn’t sure where to post The original worked fine until I realised Period 1 always had to start on 1st jan and the end of period always had to be 31st Dec regardless of what day that fell on
It’s this I can figure out how to do

 

[Yongle]

suggested solution posted in your other thread: https://www.mrexcel.com/forum/excel-questions/1054421-financial-period-start-date-3.html