Financial Period start date

A

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
3K
felixstraube
F
S
  • Solved
Count months in which an employee is active during a financial period
Replies
6
Views
792
Peter_SSs
Peter_SSs
J
  • Solved
Using Month query inside sumif formula
Replies
6
Views
742
Jpull
J
A
  • Solved
Error with working days in a month
Replies
5
Views
508
Anthony47
A
A
  • Question Question
Best approach to creating a formula(?) to loop through monthly data, if all employees have different start dates
Replies
1
Views
632
ab4100
A

Forum statistics

Threads
1,224,833
Messages
6,181,237
Members
453,026
Latest member
cknader

Share this page

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