Generate series of months by Formula

Please see attached file.  

I need help with a formula where when i put it in cell B2 and drag down and right. it will generate the YYYYMM dates for me as it is shown in the cells highlighted in yellow.

any help is appreciated.
EE6.xlsx
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Flora

So you will have date ranges in row 2 starting in column B and you want a formula to generate the months/years for those ranges in the rows below?
0
FloraAuthor Commented:
yes Norie.

my date range will be in Row 1 and then the yellow cells are the one that formula should generate.

thanks alot
0
Ejgil HedegaardCommented:
Try this.
Formula in B2.
=IF(ISTEXT(B1),("20"&MID(B1,7,2)&MID(B1,4,2))*1,(YEAR(EDATE(DATE(LEFT(B1,4)*1,RIGHT(B1,2)*1,1),1))&TEXT(MONTH(EDATE(DATE(LEFT(B1,4)*1,RIGHT(B1,2)*1,1),1)),"00"))*1)

Open in new window

If the row above is text, then the YearMonth is made from the first date in the text.
If not it is made by finding the next month from the row above, and format to YearMonth.
EE6.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
This is extra-ordinary perfect solution. exactly what i was looking for. thanks a million Ejgil.  very much appreciated.
0
Rob HensonFinance AnalystCommented:
I realise you have already accepted Ejgil's excellent solution but here is another option.

Do your values have to be text or can they be true dates but formatted just to show as "YYYYMM"?

If true dates are OK then:

=IF(B1="","",IFERROR(DATEVALUE(LEFT(B1,8)),IF(EOMONTH(B1,1)>DATEVALUE(RIGHT(B$1,8)),"",EOMONTH(B1,1))))

This can be copied down as many rows as you like rather than the specific number of rows for the range of dates; I assume the most that you would need would be 12, ie all 12 months of a year.

Then apply a custom format to the cells of "yyyymm"  (without the quotes).

See Sheet2 of attached file; I have also used Conditional Formatting on all 12 rows to highlight those with dates.

Thanks
Rob
EE6.xlsx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.