Date formatting from VBA Userform Date & time Picker Incorrect


New Member
Dec 29, 2017
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?


Well-known Member
Apr 28, 2004
Office Version
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.

Forum statistics

Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...