how do I convert a dates in a year quarters in a year?

[Guest]

For instance if I have a series of dates within a number of years, but I want
to represent them as 1st Quarter 2005, 3rd Quarter 2006 etc

 

[Peo Sjoblom]

You would need extra cells with formulas for that, there is no way of
formatting dates to quarters
Besides what are your quarters, it depends on the financial year or are
talking strictly calendar like Jan-Mar = 1st quarter

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Dave O]

Here is a formula that will produce a slightly truncated view, Q# YYYY
="Q"&ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)

This formula will derive what you ask for in your post. It's longer
because each of the 4 quarters has a different suffix (st, nd, rd, th).
=IF(ROUNDUP(MONTH(A1)/3,0)=1,"1st Quarter
"&YEAR(A1),IF(ROUNDUP(MONTH(A1)/3,0)=2,"2nd Quarter
"&YEAR(A1),IF(ROUNDUP(MONTH(A1)/3,0)=3,"3rd Quarter
"&YEAR(A1),IF(ROUNDUP(MONTH(A1)/3,0)=4,"4th Quarter "&YEAR(A1)))))