excel dates

[ghynes]

hello,

i have sheet 1 with dates in column A. i have to be able to put in column B what Quarter the date is in


on sheet 2 i have 3 columns.. Month / start date / end date.. Q1 1/1/2011 31/3/2011

how can i reference the table in sheet 2 to say in column B what quarter the date is in?

anyone help? thanks.

 

[VoG]

Hi. Try

="Q"&LOOKUP(MONTH(A1),{1,4,7,10},{1,2,3,4})

 

[ghynes]

can i put years on the Q aswell? as in 2011Q1.. because i have 3 years of data and want to seperate out by year and Q

 

[VoG]

Try

=YEAR(A1)&"Q"&LOOKUP(MONTH(A1),{1,4,7,10},{1,2,3,4})

 

[p45cal]

can i put years on the Q aswell?

I keep trying to put the years on Q as well but it's no good, I still keep on getting older

 

[VoG]

I keep trying to put the years on Q as well but it's no good, I still keep on getting older

 

[ghynes]

excellent. thanks for the help