Excel ActiveX combobox displays selected date as n

Question

I have an ActiveX combobox. Its ListFillRange is a range on another sheet. It finds the dates, but when I select them I get this weird value. Anybody know whats up and how to fix it?

I have tried fixing it through VBA code but its the same problem, I would really appreciate it.

Example code:

Private Sub ProdDateCombobox_DropButtonClick()
ProdDateCombobox.ListFillRange = "ProductionList"
End Sub

ProductionList being a range of cells D2;D100 with dates

Answer 1

The number you're getting is the number of days since 1st January 1900 - this is how Excel stores dates.

One solution to the problem is:

You have your dates, formatted as dates, in the range D2:D100 named as ProductionList.

When you select the drop-down the dates appear correctly in the drop-down, but the selected date appears as a number - 4th July 2018 showing the number 43285 (days since January 1st 1900).

• In a blank range enter the formula =TEXT(D2,"dd/mm/yyyy") where D2 is the first date in your ProductionList named range. Drag the formula down to the end of the dates.
• Copy this new range of formula and paste special as values only over your original list.

Your original list should now show correctly in the combo-box, but you'll have problems when trying to compare dates to your linked cell.

For example, if your linked cell is A5 the formula =MATCH(DATE(2018,3,1),$A$5,0) will return #N/A.

To solve this enter a formula in cell B5: =A5+0 and compare against this. The act of adding 0 to your text date will force it to recalculate as a real date.