Calculate Projected Months

[Loin75]

Hi, I am looking for some magic please.

My userform has two simple fields:

Start Month (listing all the months of the year)
Frequency (Monthly, Quarterly, Semi-Annually)

When the user has chosen both of these, I would like to display the months of the year that are relevant.

For example, if they chose Quarterly, starting November: Then it would show November, February, May, August.

I can't bend my head around this one, please help.

Many thanks

 

[RonB1111]

Assume user enters the start month in A1 and the frequency in A2.
First some setup instructions:
1. In your listing of months of the year, enter them in date format (eg: 1/1/2015, 2/1/2015, etc). Then do a custom format for those cells and cell A1 as mmmm. This will result in the user just seeing the months spelled out like "January", etc while allowing the below formula to work with a numeric cell value.
2. In your frequency list, change your entries from Monthly to 1, Quarterly to 3, and Semi-Annually to 6. Then do a custom format for each cell using only text. EG: if your listing is in A5:A7, A5 will have the number 1 entered and will have a custom format of "Monthly" (including the quotation marks), A6 will have 2 entered and will have a custom format of "Quarterly", and A7 will have 6 entered and a custom format of "Semi-Annually". Again this will result in the user just seeing the words spelled out while allowing the below formula to work with a numeric cell value.
3. Do this custom format on cell A2:

[=1] "Monthly";[=3] "Quarterly"; "Semi-Annually"

4. Assume your displayed months are in D1:O1. Custom format those cells as mmmm, then in D1 enter this formula:

=A1

and enter this formula in E1 and copy across to O1:

=IF(COLUMNS($D1:E1)>12/$A2,"",EDATE(D1,$A2))

 

[Loin75]

wow, that was magic!! Thank you very much.

I am actually struggling to understand what this is doing =IF(COLUMNS($D1:E1)>12/$A2,"",EDATE(D1,$A2))

But it works a treat.

Thanks Again.

 

[RonB1111]

You're welcome!

The 1st part of the formula makes the appropriate cells blank when it's quarterly or semi-annual. If you highlight just COLUMNS($D1:E1) in the formula bar in cells E1:O1 respectively, you'll see the results are 2,3,4,5....11. For semiannual (ie: 6 in A2), 12/A2=2 in E1 and greater than 2 in F1:O1 so F1:O1 result in blank. Same concept for quarterly. Monthly is never greater than 12/1 so every cell uses the edate part of the formula. EDATE takes the date in the cell immediately to the left and adds the number of months stated in A2 (ie: 1 for monthly, 3 for qtrly and 6 for semiannually). Hope that makes sense.

 

[Loin75]

It does, thanks.