Financial Period start date

AliBb

New Member
Joined
Apr 28, 2018
Messages
25
How can I set a period number based on a Jan - Dec Financial year where the month end date is the last Friday in the month unless the last Day of the month falls on a wed or a Thursday in which case it is the Fri of that week
For eg
Period 4 is 31st Mar 18 - 27th Apr 18
Period 5 is 28th Apr - 1st June
Period 6 is 2nd June - 29th June
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
What exactly do you need to derive:
- period number from a date; or
- period start and end dates from the period number and year?
 
Upvote 0
Formula in C5 then drag down

=D4+1

Formula in D4 then drag down

=IF(WEEKDAY(EOMONTH(C4+7,0),13)=1,EOMONTH(C4+7,0)+2,IF(WEEKDAY(EOMONTH(C4+7,0),13)=2,EOMONTH(C4+7,0)+1,EOMONTH(C4+7,0)-WEEKDAY(EOMONTH(C4+7,0),13)+3))

[TABLE="width: 350"]
<tbody>[TR]
[TD]ROW[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]period[/TD]
[TD]from[/TD]
[TD]to[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]01-01-2018[/TD]
[TD]02-02-2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]03-02-2018[/TD]
[TD]02-03-2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[TD]03-03-2018[/TD]
[TD]30-03-2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]31-03-2018[/TD]
[TD]27-04-2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5[/TD]
[TD]28-04-2018[/TD]
[TD]01-06-2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]6[/TD]
[TD]02-06-2018[/TD]
[TD]29-06-2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]7[/TD]
[TD]30-06-2018[/TD]
[TD]27-07-2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8[/TD]
[TD]28-07-2018[/TD]
[TD]31-08-2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]9[/TD]
[TD]01-09-2018[/TD]
[TD]28-09-2018[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10[/TD]
[TD]29-09-2018[/TD]
[TD]02-11-2018[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]11[/TD]
[TD]03-11-2018[/TD]
[TD]30-11-2018[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]12[/TD]
[TD]01-12-2018[/TD]
[TD]28-12-2018[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]13[/TD]
[TD]29-12-2018[/TD]
[TD]01-02-2019[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Another table with same formulas

[TABLE="width: 350"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]ROW[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]period[/TD]
[TD]from[/TD]
[TD]to[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]31-03-2018[/TD]
[TD]27-04-2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]28-04-2018[/TD]
[TD]01-06-2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[TD]02-06-2018[/TD]
[TD]29-06-2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]30-06-2018[/TD]
[TD]27-07-2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5[/TD]
[TD]28-07-2018[/TD]
[TD]31-08-2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]6[/TD]
[TD]01-09-2018[/TD]
[TD]28-09-2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]7[/TD]
[TD]29-09-2018[/TD]
[TD]02-11-2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8[/TD]
[TD]03-11-2018[/TD]
[TD]30-11-2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]9[/TD]
[TD]01-12-2018[/TD]
[TD]28-12-2018[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10[/TD]
[TD]29-12-2018[/TD]
[TD]01-02-2019[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]11[/TD]
[TD]02-02-2019[/TD]
[TD]01-03-2019[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]12[/TD]
[TD]02-03-2019[/TD]
[TD]29-03-2019[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]13[/TD]
[TD]30-03-2019[/TD]
[TD]26-04-2019[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you. Whilst this probably answers my next question I don't think I've explained myself correctly. Let me try again

I have a calendar table - columns are date, period, FinYear

the period number needs to be calculated based on the date, and the year needs to be based on the date and period number. How can I calculate a period number based on a Jan - Dec Financial year where the month end date is the last Friday in the month unless the last Day of the month falls on a wed or a Thursday in which case it is the Fri of that week
eg
Date Period FinYear
30/12/2017 1 2018
31/12/2017 1 2018
01/01/2018 1 2018

31/01/2018 1 2018
01/02/2018 1 2018
02/02/2018 1 2018
03/02/2018 2 2018

The formula you have given gives me the end date but not the period number. It is a Jan - Dec year so it is the dates that are in the previous month or next month for a given month that need to be calculated as the Month(date) doesn't work for those.
Is this possible?
 
Upvote 0
See if the following works for you (note: lightly tested):

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)
 
Upvote 0
Same table as in post # 5
Formula in B4 and drag down

=MONTH(C4+7)

[TABLE="width: 350"]
<tbody>[TR]
[TD][/TD]
[TD]TABLE1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]period[/TD]
[TD]from[/TD]
[TD]to[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]31-03-2018[/TD]
[TD]27-04-2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]28-04-2018[/TD]
[TD]01-06-2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]02-06-2018[/TD]
[TD]29-06-2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]30-06-2018[/TD]
[TD]27-07-2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]28-07-2018[/TD]
[TD]31-08-2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9[/TD]
[TD]01-09-2018[/TD]
[TD]28-09-2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10[/TD]
[TD]29-09-2018[/TD]
[TD]02-11-2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]03-11-2018[/TD]
[TD]30-11-2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12[/TD]
[TD]01-12-2018[/TD]
[TD]28-12-2018[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]29-12-2018[/TD]
[TD]01-02-2019[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2[/TD]
[TD]02-02-2019[/TD]
[TD]01-03-2019[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3[/TD]
[TD]02-03-2019[/TD]
[TD]29-03-2019[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4[/TD]
[TD]30-03-2019[/TD]
[TD]26-04-2019[/TD]
[/TR]
</tbody>[/TABLE]

ARRAY formula in H7 then drag down

=IFERROR(INDEX($B$4:$B$16,SMALL(IF(($C$4:$C$16<=$G7)*($D$4:$D$16>=$G7),ROW($B$4:$B$16),""),1)-ROW($B$4)+1),"")

[TABLE="width: 265"]
<tbody>[TR]
[TD][/TD]
[TD]TABLE2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]ROW[/TD]
[TD]DATE[/TD]
[TD]PERIOD[/TD]
[TD]year[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]29-06-2018[/TD]
[TD]6[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]30-06-2018[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]01-07-2018[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]02-07-2018[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]03-07-2018[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]04-07-2018[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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 :biggrin: 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]
 
Last edited:
Upvote 0
Here is the requested modification:

=MOD(MONTH(A2+7)-1+AND(DAY(A2+7)>27,WEEKDAY(A2+7,13)>3,DAY(A2+7)-WEEKDAY(A2+7,13)>23)-AND(DAY(A2+7)<3,WEEKDAY(A2+7,14)<3,DAY(A2+7)<=WEEKDAY(A2+7,14)),12)+1
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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