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?
 
Ejgil HedegaardConnect With a Mentor Commented:
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
 
NorieVBA ExpertCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.