Date Conversion, Date Quarters, and Adding Quarters

[mosha3]

I am using a date in an exported file; I want to convert it to an Excel recognizable format and basically add three quarters to it so it will reflect the quarter end date three quarters away.

For example:

Cell A1:20121128

I want Cell A2 to return: 09/30/2013
(Cell A1's quarter ending date is 12/31/2012, the cell should return the ending date of the quarter three quarters ahead)

I have the conversion formula (unless anyone can think of better): =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)).

I have a formula to return which quarter ending "A1+3" is in but it only returns "Q3": =CONCATENATE("Q",ROUNDUP(MONTH(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))/3,0))

Does anyone know if there is a way without using something like a reference table of the quarter end dates to do this? I.E. - all in one formula?

Any advice is very much appreciated.

Thank you,

Scott

 

[JimM]

Is this any good

=EOMONTH(DATE(LEFT(A1,4),CEILING(MID(A1,5,2)+9,3),RIGHT(A1,2)),0)

 

[mosha3]

I found that returning a date 5 years earlier.


This is what I have so far:

=CONCATENATE(ROUNDUP(MONTH(DATE(YEAR(H89),MONTH(H89)+3,DAY(H89))),0),"/",IF(MONTH(H89)<4, "31", IF(MONTH(H89)<7, "30", IF(MONTH(H89)<10, "30","31"))),"/",IF(MONTH(H89)>=10, YEAR(H89)+1, YEAR(H89)))



-Scott

 

[mosha3]

Footnote: I got this to work:


Cell B7: 20110713
Returns: 6/30/2012 (The end date of the quarter three quarters past the quarter cell B7 is in.)

=CONCATENATE(IF(MID(B7,5,2)<"04","12",IF(MID(B7,5,2)<"07","3",IF(MID(B7,5,2)<"10","6","9"))),"/",IF(MID(B7,5,2)<"04","31",IF(MID(B7,5,2)<"07","31",IF(MID(B7,5,2)<"10","30","30"))),"/",IF(MID(B7,5,2)<"04",LEFT(B7,4), LEFT(B7,4)+1))

 

[JimM]

Glad you got it to work, but still think my formula works - can anyone else give it a try just to put my mind at ease

 

[mosha3]

I don't know what I did yesterday when it was returning a date five years earlier, but it seems to work now (your formula). The only difference I get is for a date of 20??0831, 20??0731, 20??0831, 20??1031, and 20??1231, which return one month later than they should. Any idea why?

 

[JimM]

Thanks for checking - I can sleep easy now The proble you highlighted was because Aug has 31 days but the quarter end date you need ie Sep only has 30 days so it was rolling the month over to the next one! I've tweeked the formula and think it now works

=EOMONTH(DATE(LEFT(A1,4),CEILING(MID(A1,5,2)+9,3),15),0)