Calculating Finance Period Number when not calendar period number

AliBb

New Member
Joined
Apr 28, 2018
Messages
25
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]
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top