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