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
 
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

Hi!

If possible, try this too (with start values):

1) In B2 type 12 and in D2 type 1.

2) After that, enter the formulas below and copy down

In B3

=IF(WEEKDAY(A2)=6,IF(OR(EOMONTH(A2,0) < A2+5,OR(EOMONTH(A2,-1)=A2-{1;2})),(B2 < 12)*B2+1,B2),B2)
<a2+5,or(eomonth(a2,-1)=a2-{1;2})),(b2<12)*b2+1,b2),b2)

In C2

=YEAR(A2+IF(B2=12,-4,4))

In D3

=IF(WEEKDAY(A2)=6,IF(OR(EOMONTH(A2,0) < A2+5,OR(EOMONTH(A2,-1)=A2-{1;2})),(D2 < 12)*D2+1,D2),D2)
<a2+5,or(eomonth(a2,-1)=a2-{1;2})),(d2<12)*d2+1,d2),d2)


ABCDE
1DataPeriodFinYearFinPeriod
2qua 28/12/20161220161
3qui 29/12/20161220161
4sex 30/12/20161220161
5sáb 31/12/2016120172
6dom 01/01/2017120172
31qui 26/01/2017120172
32sex 27/01/2017120172
33sáb 28/01/2017220173
34dom 29/01/2017220173
58qua 22/02/2017220173
59qui 23/02/2017220173
60sex 24/02/2017220173
61sáb 25/02/2017320174
62dom 26/02/2017320174
94qui 30/03/2017320174
95sex 31/03/2017320174
367qui 28/12/20171220171
368sex 29/12/20171220171
369sáb 30/12/2017120182
370dom 31/12/2017120182
371seg 01/01/2018120182
**************************************************

<tbody>
</tbody>


I hope that the formulas above helps.

Markmzz</a2+5,or(eomonth(a2,-1)=a2-{1;2})),(d2<12)*d2+1,d2),d2)
</a2+5,or(eomonth(a2,-1)=a2-{1;2})),(b2<12)*b2+1,b2),b2)
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

Thank you. Could you explain what it’s doing please?
Also how would I determine the week number based on the start being the first date that has period 1
 
Upvote 0
The "+7" shifts the date by one week. So, for example, the week of 21-Apr-2018 falls into Period 5 as requested in Post # 9.

If I correctly understand your second question, try using the following formula for week number (assuming the start date is in cell A2):

=INT((A2-$A$2)/7)+1
 
Upvote 0
The "+7" shifts the date by one week. So, for example, the week of 21-Apr-2018 falls into Period 5 as requested in Post # 9.

If I correctly understand your second question, try using the following formula for week number (assuming the start date is in cell A2):

=INT((A2-$A$2)/7)+1

Thanks I get the +7 adds to the date but not sure what the purpose of >27, >3 or >23,or <3 parts are. How is that working? Id like to be able to work this out for myself should the criteria change for eg if the last day of the month finishes on a Tuesday the end of the month becomes the Friday of that week,

Re Week No I probably didn't explain correctly

the date:
30/12/17 is the first date in period 1 based on the formula previously (not the +7 one) therefore
30/12/17 - 5/1/2018 would all be Week 1
6/1/18 - 12/1/2018 would all be Week 2 etc

The formula you gave does give a week number but assumes the first date given is the starting point rather than working it out based on the previous formula

Thanks for all your help so far
 
Upvote 0
A small modification in the <a2+5,or(eomonth(a2,-1)=a2-{1;2})),(b2<12)*b2+1,b2),b2)
<a2+5,or(eomonth(a2,-1)=a2-{1;2})),(d2<12)*d2+1,d2),d2)
formulas in B3 and D3:

=IF(WEEKDAY($A2)=6,IF(EOMONTH(A2,0) < A2+5,(B2 < 12)*B2+1,IF(OR(EOMONTH(A2,-1)=A2-{1;2}),(B2 < 12)*B2+1,B2)),B2)

=IF(WEEKDAY(A2+7)=6,IF(EOMONTH(A2+7,0) < A2+7+5,(D2 < 12)*D2+1,IF(OR(EOMONTH(A2+7,-1)=$A2+7-{1;2}),(D2 < 12)*D2+1,D2)),D2)

Markmzz
</a2+5,or(eomonth(a2,-1)=a2-{1;2})),(d2<12)*d2+1,d2),d2)
</a2+5,or(eomonth(a2,-1)=a2-{1;2})),(b2<12)*b2+1,b2),b2)
 
Upvote 0
Here is, in brief, how the period formula works:
1) The outer function, MOD(Period-1,12)+1, converts Period 0 --> 12, Period 13 --> 1, and keeps Periods 1 through 12 as is.
2) For most dates, Period = Month, thus we have MONTH(A2)-1.
3) We need to add 1 to the month if all of the following is true for a particular date:
- date is 28th or above, thus DAY(A2)>27;
- day of week is Sat-Tue, thus WEEKDAY(A2,13)>3;
- DAY(A2)-WEEKDAY(A2,13)>23 is a fudge factor.
4) We need to subtract 1 from the month if all of the following is true for a particular date:
- date is 1st or 2nd, thus DAY(A2)<3;
- day of week is Thu or Fri, thus WEEKDAY(A2,14)<3;
- DAY(A2)<=WEEKDAY(A2,14) is a fudge factor.


To derive the week number from a date, try the following formula; it assumes you have the date in cell A2 and its corresponding FinYear in cell C2:

=INT((A2-WORKDAY.INTL(DATE(C2,1,)-LOOKUP(WEEKDAY(DATE(C2,1,),13),{1,3},{1,-1}),LOOKUP(WEEKDAY(DATE(C2,1,),13),{1,3},{1,-1}),"1111011")-1)/7)+1
 
Upvote 0
Hi!

Maybe the formulas below can helps (without start values):

In B2

=MOD(MONTH(A2)-1+(EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),16) < A2)-
SUM((WEEKDAY(EOMONTH(A2-{0;1;2},0))={6;5;4})*(A2 > EOMONTH(A2-{0;1;2},0)-{7;6;5})),12)+1
<a2)-


In C2

=YEAR(A2+IF(B2=12,-4,4))

In D2

=MOD(MONTH(A2+7)-1+(EOMONTH(A2+7,0)-WEEKDAY(EOMONTH(A2+7,0),16) < A2+7)-
SUM((WEEKDAY(EOMONTH(A2+7-{0;1;2},0))={6;5;4})*(A2+7 > EOMONTH(A2+7-{0;1;2},0)-{7;6;5})),12)+1
<a2+7)-

In E2

=CEILING(A2-DATE(C2,1,3-WEEKDAY(DATE(C2,1,),13)),7)/7


Markmzz</a2+7)-
</a2)-
 
Upvote 0
How can I amend this so that period 12 always finishes on 31st Dec and Period 1 always starts on 1st Jan?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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