Date formula: return Quarter and Fiscal Year of a date

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!

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.


"Rob" wrote:

> 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!
>
>

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!

"Huw Davies" wrote:

> 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.
>
>
> "Rob" wrote:
>
> > 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!
> >
> >

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.

"Rob" wrote:

> 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!
>
> "Huw Davies" wrote:
>
> > 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.
> >
> >
> > "Rob" wrote:
> >
> > > 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!
> > >
> > >

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)))))

"Rob" wrote:

> 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!
>
> "Huw Davies" wrote:
>
> > 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.
> >
> >
> > "Rob" wrote:
> >
> > > 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!
> > >
> > >

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

"Rob" wrote:

> 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!
>
> "Huw Davies" wrote:
>
> > 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.
> >
> >
> > "Rob" wrote:
> >
> > > 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!
> > >
> > >

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)


Rob wrote:
>
> 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!

--

Dave Peterson

Thanks to all for their suggestions - this formula will save me tons of time.

Rob

"Dave Peterson" wrote:

> 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)
>
>
> Rob wrote:
> >
> > 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!
>
> --
>
> Dave Peterson
>