# Determine if combobox selection is a future month in Excel

OK...without going into all of the details of exactly what I'm doing:

I have a combobox on an Excel worksheet that lists the months January, February, March, etc.   The combobox references a range of cells (let's say B1 through B12) to be able to display the values to choose from.

I want to be able to do this without a macro.

If the month selected from the combobox is the current month or a future month, I want to display an error.

So, for example, right now we're in November.  If the user selects November, I want to display a message like "Invalid month" or something.   It's not the displaying of a message I'm having trouble with. It's how to get the program to determine that November is equal to the current month or that December (if that month was selected) is greater than November, etc.

In other words, if I was using numeric values for the months (January being "1", February being "2", etc), it would be easy to determine greater than, equal to, less than etc.   But since I'm using text for the months, I don't know how to make the comparison.

Thanks
###### Who is Participating?

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.

Commented:
With the result from the combo box in C1

=IF(C1>=MONTH(NOW()),"Invalid month","")

Experts Exchange Solution brought to you by

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

Commented:
Hi,

pls try
``````=IF(MONTH(DATEVALUE("1 "&C1&" 2000"))>=MONTH(NOW()),"Invalid month","")
``````
But you won't be able to enter anything in January

Regards
Author Commented:
Ejgil,

The result from the combobox in C1 is the name of month (i.e. January, February, etc).

MONTH(NOW())  assigns a numeric value.

As an example, let's say I selected "November".    November will then appear in C1.   Your function (unless I'm missing something it comparing "November"  to  11  (i.e. MONTH(NOW()) = 11), which doesn't work.
Author Commented:
OK.  I got it working.   Ejgil's partial solution needs this additional function to compare numbers to numbers:

In another/adjacent cell, put in the following:

=MONTH(DATEVALUE(cell name and number that the text name of the month is in &" 1"))

Then use Ejgil's code to compare to the cell the above function is in.
Older than dirtCommented:
I'm not looking for any points here, but you could use  VBA and data validation to create a dropdown list that will not show future months.

This code assumes the month names are in A1:A12 and that you want the dropdown list in B1.
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range
Dim strMonths As String

If Not Intersect(Target, Range("B1")) Is Nothing Then
For Each cel In Range("A1:A12")
If Month(DateValue("01 " & cel.Text & " 2017")) <= Month(Now) Then
If strMonths <> "" Then
strMonths = strMonths & "," & cel.Text
Else
strMonths = cel.Value
End If
End If
Next

With Target.Validation
.Delete
End With

End If

End Sub
``````
Finance AnalystCommented:
Based on Martin's suggestion of having a dynamic list to choose from but you mentioned you don't want VBA, you can create the list of months with formulas.

B1   =DATE(YEAR(TODAY()),1,1)
B2   =IF(B1="","",IF(EOMONTH(B1,1)>TODAY(),"",EOMONTH(B1,1)))

Copy B2 down through B3 to B12.

Format these cells as "mmmm" so that only the month shows.

Set the combo box to look at range B1 to B12 and set number of rows to 12, there will be some that are blank but at least the user will be able to see all rather than having to scroll.

With a combo box the result of the choice is numeric anyway, the result is the position of the choice in the list. So for example if you chose February the result would be 2.
###### 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.