Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|