Date formula: return Quarter and Fiscal Year of a date

G

Guest

Hello,

I'm having problems with the following... my company has a 9/30 fiscal year
end. I am trying to create a formula that looks at a date that will be input
and will return the following:
- The fiscal quarter
- the fiscal year.
For example, A2 has a date of 10/28/06. I would like a formula to return
the fiscal quarter and year: Quarter 1 FY06.

Thanks in advance for your help!
 
G

Guest

Rob, based on the assumption that your date is in Cell A1, I think the
following might work.
=CONCATENATE(IF(MONTH(A1)<4,"Quarter 2",IF(MONTH(A1)<7,"Quarter
3",IF(MONTH(A1)<10,"Quarter 4","Quarter 1")))& " FY"&YEAR(A1))

Unfortunately, I can't get it to show anything other than FY2005, rather
than FY05, but I think it does everything else.

Hope this helps,

Huw.
 
G

Guest

Huw,

Thanks very much, but still having trouble with the year.

With your formula, the Quarter always seems to work, but the fiscal year
doesn't work properly. For example, 10/1/2005 is the first day of Quarter 1
FY2006, but your formula pulls the year as 2005. Any ideas on fixing the
year issue?

Thx!
 
G

Guest

Rob,

Sorry, I missed that first time around - here's a modified version with the
concatenation built into each IF test. If it calculates it should be Quarter
1, then it automatically adds a 1 to the year as well.

Hope it works this time.

Huw.
 
G

Guest

Oops - a bit too quick there...

Here's the revised version...

=(IF(MONTH(A1)<4,("Quarter 2 FY"&YEAR(A1)),IF(MONTH(A1)<7,("Quarter 3
FY"&YEAR(A1)),IF(MONTH(A1)<10,("Quarter 4 FY"&YEAR(A1)),("Quarter 2
FY"&YEAR(A1)+1)))))
 
G

Guest

A little bit shorter

="Q"&INDEX({2;3;4;1},INT((MONTH(A1)+2)/3))&" -
FY"&IF(INT((MONTH(A1)+2)/3)=4,YEAR(A1)+1,YEAR(A1))


Regards,

Peo Sjoblom
 
D

Dave Peterson

Another version:
="FY"&YEAR(A1)+MONTH(A1>=10)&"--Q"&INT(1+MOD(MONTH(A1)-10,12)/3)

This returns:
FY2006--Q2
(nice for sorting, I think)

But if you want your string:
="Quarter "&INT(1+MOD(MONTH(A16)-10,12)/3)
&" FY"&TEXT(MOD(YEAR(A16)+MONTH(A16>=10),1000),"00")
(all one cell)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top