Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Rob
 
Posts: n/a
Default 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!


  #2   Report Post  
Huw Davies
 
Posts: n/a
Default

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!


  #3   Report Post  
Rob
 
Posts: n/a
Default

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!


  #4   Report Post  
Huw Davies
 
Posts: n/a
Default

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!


  #5   Report Post  
Huw Davies
 
Posts: n/a
Default

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!




  #6   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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!


  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
Rob
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"