This question has been asked many times but my solution is not working...
here is my formula
="FY"&YEAR(AH2)+MONTH(AH2>=7)&"--Q"&INT(1+MOD(MONTH(AH2)-7,12)/3)
Im trying to have a formula that calculates FYXX QX with the fiscal year ending June30th
This formula is saying 5/1/10 is fiscal year 11 instead of 10
THX
Last edited by brotherwo; 01-07-2011 at 05:57 PM.
mAYBE THIS:
="FY"&YEAR(AH2)&"--Q"&1+INT((MONTH(AH2)-1)/3)
Now the Quarter is screwyOriginally Posted by zbor
How exactly?
Please Login or Register to view this content.
That is international... Im looking for US format. 10/1/2010 is October 1st Q4
But mine solution does not depend of format and it will return Q4 for 1st October aswell
Sorry I messed up...
June 30th is year end so............. October is Q2 Jan Q3 Apr Q4
solved it
="FY"&YEAR(AH2)+MONTH(AH2>=7)&"--Q"&INT(1+MOD(MONTH(AH2)-7,12)/3)
never mind...year is screwed up.... and for 3/1/10 it is giving me FY 11
ahhhhhhhhhhhhhhhhh
Last edited by brotherwo; 01-07-2011 at 05:26 PM.
Try this
="FY"&YEAR(AH2+184)&"--Q"&LOOKUP(MONTH(AH2),{1,4,7,10;3,4,1,2})
Audere est facere
Awesome! Thanks so much
This also:
="FY"&YEAR(A1)&"--Q"&1+MOD(2+INT((MONTH(A1)-1)/3),4)
Zbor, I think the year is July to June so 30th June 2010 is the last day of the 4th quarter of "2010" while 1st July 2010 is the first day of the first quarter of 2011, hence YEAR(AH2+184) in my suggested formula.
That's also the intent of this part of brotherwo's formula
YEAR(AH2)+MONTH(AH2>=7)
but that fails because it doesn't have parentheses in the right places. If you change to
YEAR(AH2)+(MONTH(AH2)>=7)
then that will do the same as my suggestion
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks