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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ejgil HedegaardCommented:
With the result from the combo box in C1

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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial

pls try
=IF(MONTH(DATEVALUE("1 "&C1&" 2000"))>=MONTH(NOW()),"Invalid month","") 

Open in new window

But you won't be able to enter anything in January

dbfromnewjerseyAuthor Commented:

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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dbfromnewjerseyAuthor 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.
Martin LissOlder 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
                strMonths = cel.Value
            End If
        End If
    With Target.Validation
        .Add Type:=xlValidateList, Formula1:=strMonths
    End With

End If

End Sub

Open in new window

Rob HensonFinance 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.