Date formatting from VBA Userform Date & time Picker Incorrect

[avg95]

I'm creating a database in excel where each record has an associated year/period end. I've created a user form to collect the data for each record and the user form contains a date & time picker to set the year end date.

The code of my user form writes the date picker value to a global variable as follows:

 RecordYearEnd = DP_YEDate.Value

A second module then writes this value to the relevant row in a table.

Sheets("Data").Cells(IDRow, 7).Value = RecordYearEnd
Sheets("Data").Cells(IDRow, 7).NumberFormat = "dd/mm/yyyy"

When I go to my table, both the cell and the date appear to be formatted correctly, but when I go to the filter button at the top of the column, the value is treated as text. Is there a way I can alter my code to ensure the dates are treated correctly?

 

[Norie]

How is RecordYearEnd declared?

Generally when handling dates coming from a userform you need to convert the 'text' date to a 'real' date so that when it's put on the worksheet it's treated correctly.

You can do that using a conversion function like CDate or with DateValue.

 

[Kenneth Hobson]

Sheets("Data").Cells(IDRow, 7).Value = CDate(RecordYearEnd)

 

[avg95]

This is perfect, thank you both!