date formula help

[KC]

J4 will have a date in it for example 11/02/2009 or 04/12/2010

In cell M4 I need a formula that will look at J4 and return the qtr & year,
ie 4Q2009 05 2Q2010, this will go into 2010 & 11, so I need the formula to
accomodate any date and year.

Thanks.

 

[Per Jessen]

Hi

Try this formla:

=ROUNDUP(MONTH(J4)/4,0) & "Q" & YEAR(J4)

Regards,
Per

 

[Ron@Buy]

Not quite sure about your desired result ie why 05 2Q2010.
Anyhow put this formula in M4 to achieve the first part of your problem:
=CEILING(MONTH(J4)/3,1)&"Q"&TEXT(J4,"yyyy")

 

[KC]

Thanks Ron, sorry, don't know why I had that 05 in there, it shouldn't have
been. The formula works great, but I should have asked if there is nothing
in cell J4, then is there a way to leave the cell blank? I tried an ISblank,
but it didn't seem to work.

 

[Ron@Buy]

Mike H came up with an alternative shorter solution:
=INT((MONTH(J4)-1)/3)+1&"Q"&YEAR(J4)
To return a blank if J4 is empty try this:
=IF(J4="","",INT((MONTH(A1)-1)/3)+1&"Q"&YEAR(A1))
or
=IF(J4="","",CEILING(MONTH(J4)/3,1)&"Q"&TEXT(J4,"yyyy"))