Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |