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
 
Sort by date Sort by votes
AliBb said:
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
Click to expand...

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)


[TABLE="class: grid, width: 394"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Period[/TD]
[TD="align: center"]FinYear[/TD]
[TD="align: center"]FinPeriod[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]qua 28/12/2016[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]qui 29/12/2016[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]sex 30/12/2016[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]sáb 31/12/2016[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]dom 01/01/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]qui 26/01/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"]sex 27/01/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"]sáb 28/01/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"]dom 29/01/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]58[/TD]
[TD="align: center"]qua 22/02/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]59[/TD]
[TD="align: center"]qui 23/02/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]60[/TD]
[TD="align: center"]sex 24/02/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]61[/TD]
[TD="align: center"]sáb 25/02/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]62[/TD]
[TD="align: center"]dom 26/02/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]94[/TD]
[TD="align: center"]qui 30/03/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]95[/TD]
[TD="align: center"]sex 31/03/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]367[/TD]
[TD="align: center"]qui 28/12/2017[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]368[/TD]
[TD="align: center"]sex 29/12/2017[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]369[/TD]
[TD="align: center"]sáb 30/12/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]370[/TD]
[TD="align: center"]dom 31/12/2017[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]371[/TD]
[TD="align: center"]seg 01/01/2018[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]*****[/TD]
[TD="align: center"]****************[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Tetra201 said:
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
Click to expand...

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
Tetra201 said:
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
Click to expand...

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
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
740
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,825
Messages
6,181,189
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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