Change Date to Fiscal Year
I am having some serious issues trying to convert a specific date to a FY Year date. For instance, Jan, Feb, Mar is 3rd Quarter of the prior year. So, Jan 2013 is actually Q3-12. Please help! I was trying to use an IF statement but just cant get it right: =(IF("Q3-"&MONTH(S1004)<4,(YEAR(S1004)),IF("Q4-"&MONTH(S1004)<7,(YEAR(S1004)),IF("Q1-"&MONTH(S1004)<10,(YEAR(S1004)),IF("Q2-"&MONTH(S1004)<12,(YEAR(S1004))))))) doesnt work
Thanks in advance!!!
try this
=(IF(MONTH(A1)<4,"Q3-"&(YEAR(A1)-1),IF(MONTH(A1)<7,"Q4-"&(YEAR(A1))-1,IF(MONTH(A1)<10,"Q1-"&(YEAR(A1)),IF(MONTH(A1)<13,"Q2-"&(YEAR(A1)))))))
Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.
Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
If you're happy with an answer given, please click the * under the person's name to boost their reputation.
How about:-
in A1 I've got the date that you want to change the formatting. In B1 I've got the FY start date, assuming its the first of a month.
In B3 I put the following formula:-
="Q" & INT((MONTH(A1)+12*(YEAR(A1)>YEAR(B1))-MONTH(B1)+3)/3) & "-" & YEAR(B1)
Hope this helps.
Chris
This works perfectly Craig!!! Thanks!!!!
@Chris, in B1, do you mean if August 1 is the FY start date then you would put August 1? no year?
In B1 put the start of the current financial year. with the year.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks