tag:blogger.com,1999:blog-1869924468172210809.post1271722029229839111..comments2021-04-29T08:11:38.084+01:00Comments on Not Just Numbers: Excel Tip: Identifying your financial period from a dateGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-1869924468172210809.post-65717690400956520152014-08-06T14:01:30.956+01:002014-08-06T14:01:30.956+01:00Yusuph
I presume you mean that the year starts on...Yusuph<br /><br />I presume you mean that the year starts on 1st July, or you would only have 11 months!<br /><br />Assuming this is the case, the example for non-calendar year with calendar months applies. i.e.<br /><br />So, if you replace the 3 with the month of your year-end (i.e. 6) and the 9 with 12 minus the month of your year-end (12-6=6) then your formula becomes:<br /><br />=IF(MONTH(A2)>6,MONTH(A2)-3,MONTH(A2)+6)Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-14883964594795589532014-08-06T13:57:05.796+01:002014-08-06T13:57:05.796+01:00Andy, some impressive stuff there for calculating ...Andy, some impressive stuff there for calculating a 4-4-5 periods. As you can see in the post, I suggest the lookup option when it gets this complicated, but you still need to work out what the period-ends should be to enter into the lookup table!Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-43761897958192901552014-08-06T13:02:49.462+01:002014-08-06T13:02:49.462+01:00NOW, OUR FINANCIAL YEAR STARTS ON 31 JULY AND ENDS...NOW, OUR FINANCIAL YEAR STARTS ON 31 JULY AND ENDS ON 30TH JUNE EVERY YEAR. PLSE, GIVE US A RIGHT FORMULA.<br />REGARDS.Anonymoushttps://www.blogger.com/profile/12313885651826861896noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-87341087971961542302014-08-06T09:08:57.007+01:002014-08-06T09:08:57.007+01:00I used to work with a 4-4-5 financial period and t...I used to work with a 4-4-5 financial period and that was much harder to calculate, although you could use this formula =MATCH(A1-DATE(YEAR(A1),1,1),{0,29,57,92,120,148,183,211,239,274,302,330}) <br /><br />However the above does group the last few days of the year (30th 31st Dec etc) in to the last week of the last period, and this didn't quite work for me as the company I worked for would rather have the last week of the year a partial week and the new week started on the 1st monday of the month rather than the 1st Jan.<br />So I just used to use some vba or a lookup formula to a stored calendar lookup spreadsheet where I had all the dates, periods and bank holidays precalculated for the next 10 years. to make things more difficult they also used Julian dates, so this formula also came in handy...<br />=DATE(2000+LEFT(A2,2),1,RIGHT(A2,3)) <br />or if looking at historical dates you can use<br />=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))<br />(julian date example -YYxxx (xxx = day of year) = 14032 = 2nd Feb 2014)<br />Anonymoushttps://www.blogger.com/profile/16951265273601088232noreply@blogger.com