# Calculate Projected Months

#### Loin75

##### Active Member
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.

Many thanks

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:
Code:
``[=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:
Code:
``=A1``
and enter this formula in E1 and copy across to O1:
Code:
``=IF(COLUMNS(\$D1:E1)>12/\$A2,"",EDATE(D1,\$A2))``

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.

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.

It does, thanks.

Replies
4
Views
589
Replies
13
Views
610
Replies
3
Views
648
Replies
1
Views
3K
Replies
2
Views
517

1,196,498
Messages
6,015,557
Members
441,900
Latest member
Inaschemitex2023

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back