Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting quarter dates in excel
Hi,
I have the following (complex!!) formula to work out quarters from dates; =IF(ROUNDUP(MONTH(G2)/3,0)=1,"1st Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,"2nd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,"3rd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,"4th Quarter "&YEAR(G2))))) My question is - how can I change it so that Q1 begins on the 1 November (the begining of my corps financial year), and not the begining of the calender year (jan 01)? Many thanks RB |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting quarter dates in excel
Why don't you use a lookup table to identify the Quarter.
Insert a sheet in your workbook with the folowing data 11 Q1 12 Q1 1 Q1 2 Q2 etc. Then use a formula like =vlookup(month(G2),<<put your table array here ,2) This should retur the quarter and it makes your formula a bit simpler. Hans "RGB" wrote: Hi, I have the following (complex!!) formula to work out quarters from dates; =IF(ROUNDUP(MONTH(G2)/3,0)=1,"1st Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,"2nd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,"3rd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,"4th Quarter "&YEAR(G2))))) My question is - how can I change it so that Q1 begins on the 1 November (the begining of my corps financial year), and not the begining of the calender year (jan 01)? Many thanks RB |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting quarter dates in excel
=CHOOSE(INT(MOD(MONTH(A1)-11,12)/3)+1,"1st","2nd","3rd","4th")&" Quarter
"&YEAR(A1) HTH -- AP "RGB" a écrit dans le message de news: ... Hi, I have the following (complex!!) formula to work out quarters from dates; =IF(ROUNDUP(MONTH(G2)/3,0)=1,"1st Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,"2nd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,"3rd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,"4th Quarter "&YEAR(G2))))) My question is - how can I change it so that Q1 begins on the 1 November (the begining of my corps financial year), and not the begining of the calender year (jan 01)? Many thanks RB |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting quarter dates in excel
I may have overcomplicated it, but I think this works as expected:
=ROUNDUP(MONTH(DATE(2000,MONTH(A1)+1,1))/3,0) & CHOOSE(ROUNDUP(MONTH(DATE(2000,MONTH(A1)+1,1))/3,0),"st","nd","rd","th") & " Quarter " & YEAR(A1) + INT(MONTH(A1)/12) If you don't want the number suffix the formula is a little less complex. I have also added the final part to put december in the right year. Hope this helps, Miguel. "RGB" wrote: Hi, I have the following (complex!!) formula to work out quarters from dates; =IF(ROUNDUP(MONTH(G2)/3,0)=1,"1st Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,"2nd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,"3rd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,"4th Quarter "&YEAR(G2))))) My question is - how can I change it so that Q1 begins on the 1 November (the begining of my corps financial year), and not the begining of the calender year (jan 01)? Many thanks RB |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting quarter dates in excel
Thanks that works great - one problem though. November & December of each
calender year are actually in the next Finacial year. E.g. The calender date November 1st 2007 is actually in Q1 2008. How can I ammend the formula to reflect this? Many Thanks RB "Miguel Zapico" wrote: I may have overcomplicated it, but I think this works as expected: =ROUNDUP(MONTH(DATE(2000,MONTH(A1)+1,1))/3,0) & CHOOSE(ROUNDUP(MONTH(DATE(2000,MONTH(A1)+1,1))/3,0),"st","nd","rd","th") & " Quarter " & YEAR(A1) + INT(MONTH(A1)/12) If you don't want the number suffix the formula is a little less complex. I have also added the final part to put december in the right year. Hope this helps, Miguel. "RGB" wrote: Hi, I have the following (complex!!) formula to work out quarters from dates; =IF(ROUNDUP(MONTH(G2)/3,0)=1,"1st Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=2,"2nd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=3,"3rd Quarter "&YEAR(G2),IF(ROUNDUP(MONTH(G2)/3,0)=4,"4th Quarter "&YEAR(G2))))) My question is - how can I change it so that Q1 begins on the 1 November (the begining of my corps financial year), and not the begining of the calender year (jan 01)? Many thanks RB |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting quarter dates in excel
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort dates in Excel | Excel Discussion (Misc queries) | |||
I know Excel can help me with keeping track of due dates | Excel Discussion (Misc queries) | |||
Excel Sheet default setting. | Excel Discussion (Misc queries) | |||
Excel 2003 Trashes Imported Dates | Excel Discussion (Misc queries) | |||
Ensure dates imported into Excel 2003 are shown in DD/MM/YYY form. | Excel Discussion (Misc queries) |