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

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>

 

[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