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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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))

ROWBCD
3periodfromto
4101-01-201802-02-2018
5203-02-201802-03-2018
6303-03-201830-03-2018
7431-03-201827-04-2018
8528-04-201801-06-2018
9602-06-201829-06-2018
10730-06-201827-07-2018
11828-07-201831-08-2018
12901-09-201828-09-2018
131029-09-201802-11-2018
141103-11-201830-11-2018
151201-12-201828-12-2018
161329-12-201801-02-2019

<tbody>
</tbody>
 
Last edited:
Upvote 0
Another table with same formulas

ROWBCD
3periodfromto
4131-03-201827-04-2018
5228-04-201801-06-2018
6302-06-201829-06-2018
7430-06-201827-07-2018
8528-07-201831-08-2018
9601-09-201828-09-2018
10729-09-201802-11-2018
11803-11-201830-11-2018
12901-12-201828-12-2018
131029-12-201801-02-2019
141102-02-201901-03-2019
151202-03-201929-03-2019
161330-03-201926-04-2019

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
 
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)

TABLE1
ROWBCD
3periodfromto
4431-03-201827-04-2018
5528-04-201801-06-2018
6602-06-201829-06-2018
7730-06-201827-07-2018
8828-07-201831-08-2018
9901-09-201828-09-2018
101029-09-201802-11-2018
111103-11-201830-11-2018
121201-12-201828-12-2018
13129-12-201801-02-2019
14202-02-201901-03-2019
15302-03-201929-03-2019
16430-03-201926-04-2019

<tbody>
</tbody>

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),"")

TABLE2
GHI
ROWDATEPERIODyear
729-06-201862018
830-06-201872018
901-07-201872018
1002-07-201872018
1103-07-201872018
1204-07-201872018

<tbody>
</tbody>
 
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
21/04/2018
4
2018
5
22/04/2018
4
2018
5
23/04/2018
4
2018
5
24/04/2018
4
2018
5
25/04/2018
4
2018
5
26/04/2018
4
2018
5
27/04/2018
4
2018
5
28/04/2018
5
2018
5
29/04/2018
5
2018
5

<tbody>
</tbody>
 
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,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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