Function to Convert Dates

[rhkan]

I created a dropdown box that was pulling data from a cell range that had dates in this format: MM/DD/YYYY. However, when I clicked on the dropdown box, the dates appeared in numerical form (EX: 10/25/02 appeared as 37554).

These are my 2 questions:
1) Is there a property that can be used to change this from numerical format to the MM/DD/YYYY format in the dropdown box?
2) Is there a VB function that also be used to change this from numerical format to the MM/DD/YYYY format in a cell and/or dropdown box?

Thanks!

 

[Yogi Anand]

Hi rhkan:

To convert the number (representing a date in a cell), you can ...

1. select the cell -- and CUSTOM FORMAT the cell as mm/dd/yyyy
or
2. using VBA -- ActiveCell.NumberFormat="mm/dd/yyyy"

I hope this helps. If I have misunderstood your question -- my apologies.

 

[Ekim]

You are either using a Validation dropdown (Data menu | Validation) or a Combo box from the Toolbox menu.

1. Validation dropdown

Simply format the target cell as a date.

2. Combo Box

Assuming that the combo box is named Combobox1 and is located on sheet 1, put the following macro in the Sheet1 module:

Private Sub ComboBox1_Change()

    With ActiveSheet.ComboBox1
        .Value = Format(.Value, "mm/dd/yyyy")
    End With
    
End Sub

As far as I am aware, there is no Combo Box property that changes a date format. It must be done via VBA.

HTH

Mike