Formula for Dates

[KC]

Cell L4 has a date in it, I'd like a formula that can look at the date and
return the quarter & year. For instance 10/15/2009 would be 3Q2009,
03/12/2010 would be 1Q2010. Thanks very much!

 

[Mike H]

Hi,

I'm not sure what you want, to return the quarter use

=INT((MONTH(A1)-1)/3)+1

to return your string use

=INT((MONTH(A1)-1)/3)+1&"Q"&YEAR(A1)

Mike

 

[Lars-Åke Aspelin]

Cell L4 has a date in it, I'd like a formula that can look at the date and
return the quarter & year. For instance 10/15/2009 would be 3Q2009,
03/12/2010 would be 1Q2010. Thanks very much!

Try this formula:

=INT((MONTH(L4)-1)/3+1)&"Q"&YEAR(L4)

October 15 will be Q4, not Q3, though.

Hope this helps / Lars-Åke

 

[Bernd P]

=INT((MONTH(L4)+2)/3)&"Q"&YEAR(L4)

Regards,
Bernd