G Guest Apr 3, 2007 #1 Is there a way to convert dates into their respective fiscal quarters. For instance, if A2 = 10/15/07, I want A3 = Q3.
Is there a way to convert dates into their respective fiscal quarters. For instance, if A2 = 10/15/07, I want A3 = Q3.
G Guest Apr 3, 2007 #2 I'm doing this for a calendar quarter as opposed to a fiscal quarter. I'm not sure of your fiscal calendar. =IF(MONTH(A2)>=10,"Q4",IF(MONTH(A2)>=7,"Q3",IF(MONTH(A2)>=4,"Q2","Q1")))
I'm doing this for a calendar quarter as opposed to a fiscal quarter. I'm not sure of your fiscal calendar. =IF(MONTH(A2)>=10,"Q4",IF(MONTH(A2)>=7,"Q3",IF(MONTH(A2)>=4,"Q2","Q1")))
G Guest Apr 3, 2007 #3 Define your quarters: Assume Q1 = 1/1/2007 to 3/31/2007, etc., etc. So, =IF(A1>10/1/2007,"Q4",IF(AND(A1>7/1/2007,A1<=9/30/2007),Q3,IF(AND(A1>4/1/2007,A1<7/1/2007),Q2,Q1))) Dave
Define your quarters: Assume Q1 = 1/1/2007 to 3/31/2007, etc., etc. So, =IF(A1>10/1/2007,"Q4",IF(AND(A1>7/1/2007,A1<=9/30/2007),Q3,IF(AND(A1>4/1/2007,A1<7/1/2007),Q2,Q1))) Dave
P Peo Sjoblom Apr 3, 2007 #4 Yes it can be done, I assume your fiscal years starts on April 1st ="Q"&INDEX({4;1;2;3},INT((MONTH(A2)+2)/3))
Yes it can be done, I assume your fiscal years starts on April 1st ="Q"&INDEX({4;1;2;3},INT((MONTH(A2)+2)/3))
G Guest Apr 3, 2007 #5 With a date in cell A1 Try something like this: B1: ="Q"&CEILING(MONTH(A1)/12*4,1) Does that help? *********** Regards, Ron XL2002, WinXP
With a date in cell A1 Try something like this: B1: ="Q"&CEILING(MONTH(A1)/12*4,1) Does that help? *********** Regards, Ron XL2002, WinXP
G Guest Apr 3, 2007 #7 Ooops! I totally missed the "fiscal year" situation. Nice catch (and good solution), Peo! *********** Regards, Ron XL2002, WinXP
Ooops! I totally missed the "fiscal year" situation. Nice catch (and good solution), Peo! *********** Regards, Ron XL2002, WinXP
H Harlan Grove Apr 3, 2007 #8 Peo Sjoblom said: Yes it can be done, I assume your fiscal years starts on April 1st ="Q"&INDEX({4;1;2;3},INT((MONTH(A2)+2)/3)) Click to expand... More generally, if the fiscal year beginning date (text in MM/DD format) were given by the name FYBD, the fiscal quarter would be given by =TEXT((DATEDIF(FYBD&"/1904",$A2,"YM")+2)/3,"\Q0")
Peo Sjoblom said: Yes it can be done, I assume your fiscal years starts on April 1st ="Q"&INDEX({4;1;2;3},INT((MONTH(A2)+2)/3)) Click to expand... More generally, if the fiscal year beginning date (text in MM/DD format) were given by the name FYBD, the fiscal quarter would be given by =TEXT((DATEDIF(FYBD&"/1904",$A2,"YM")+2)/3,"\Q0")
