asked on

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

Norie

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?

Flora Edwards

ASKER

yes Norie.

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

thanks alot

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

ASKER

This is extra-ordinary perfect solution. exactly what i was looking for. thanks a million Ejgil. very much appreciated.

Rob Henson

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