date formula help
 

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.

date formula help
 

Hi

Try this formla:

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

Regards,
Per

"KC" skrev i meddelelsen
...
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.

date formula help
 

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" wrote:

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.

date formula help
 

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" wrote:

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" wrote:

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.

date formula help
 

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"))


"KC" wrote:

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" wrote:

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" wrote:

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.