Menu

Setting quarter dates in excel

Search this Thread

Prev

Previous Post

Next Post

Next

#5

Report Post

Posted to microsoft.public.excel.misc

Ardus Petus

Posts: n/a

Default

Setting quarter dates in excel

My formula does that!

Cheers,
--
AP

"RGB" a écrit dans le message de news:
...
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

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I sort dates in Excel	Lorene Schertzl	Excel Discussion (Misc queries)	3	March 30th 06 01:52 AM
I know Excel can help me with keeping track of due dates	pamiam3333	Excel Discussion (Misc queries)	1	March 7th 06 05:17 AM
Excel Sheet default setting.	vishu	Excel Discussion (Misc queries)	2	September 29th 05 04:36 PM
Excel 2003 Trashes Imported Dates	KymY	Excel Discussion (Misc queries)	3	April 27th 05 02:34 PM
Ensure dates imported into Excel 2003 are shown in DD/MM/YYY form.	KymY	Excel Discussion (Misc queries)	3	February 11th 05 02:28 PM

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

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement