DEFINITION ACCORDING TO MONTH IN CALENDAR YEAR
A quarter is per def. 1/4 of someting. Normally a year is divided
akording to month. I.e. a quarter is defined as 3 month. A Quarters in
a Calendar year is the defined like this
Q1 - Jan/march
Q2 - Apr/Jun
Q3 - Jul/Sep
Q4 - Oct/Dec
(The same results will occure if you use days as base on the Quarter
definitions except for Years with 366 days. Then the Q3 starts on july
2! - I do not propose to use this.)
What quarter you are taklking about is depend on what your're talking
about. Many financial years do not follow the calendar year.
PROPOSAL - FORMULA IN EXCEL
If your date is in cell A4 use this formula to extract the quarter
number:
=ROUNDUP(MONTH(A4)/3,0)
To write it in the "ISO" way (Quarters are not defined in the ISO
standard) you may do it like this
=YEAR(A4) & "-Q" & ROUNDUP(MONTH(A4)/3,0)
HOW TO WRITE DATES, WEEKS, ORDINAL DATES, TIME ...
You will gain a lott if you try to stick to the ISO 8601 Standard on
how to write Dates. Here's a good Explanation
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
Here you have a Calendar that follow the Week numbering system
according to this Standard
http://www.pvv.org/~nsaa/Calendar_2004-04-06.xls
MORE REFERENCES
ISO 8601 - Discussed -
http://www.iso.ch/iso/en/prods-services/popstds/datesandtime.html
ISO 8601 - Dicussion Group -
http://dmoz.org/Science/Reference/Standards/Individual_Standards/ISO_8601/
ISO 8601 - A Copy of the last Final Draft'ISO 8601:2000 Second Edition
[Final Draft 2000-12-15] (PDF 185KB)'
(
http://www.pvv.org/~nsaa/8601v2000.pdf)
Attachment filename: datetoquarter.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=535563