Period Number =MOD(MONTH(A2)-1+AND(DAY(A2)>27,WEEKDAY(A2,13)>3,DAY(A2)-WEEKDAY(A2,13)>23)-AND(DAY(A2)<3,WEEKDAY(A2,14)<3,DAY(A2)<=WEEKDAY(A2,14)),12)+1
FinYear =YEAR(A2)+AND(MONTH(A2)=12,B2=1)-AND(MONTH(A2)=1,B2=12)
Thank you this works a treat
Just wish I knew how!
I have a further column (FinPeriod) where the period number changes 1 week before the date calculated for the Period
How can I amend what you have created for Period to take this into account?
eg
[TABLE="class: cms_table, width: 276"]
<tbody>[TR]
[TD="align: right"]21/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]22/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]23/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]24/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]25/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]26/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]27/04/2018
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]28/04/2018
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]29/04/2018
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]2018
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]