Formatting a date from userform, then filtering.

Hello Guys!

Got a slight problem which I cannot seem to get my head around. I have a userform which writes a date into a cell within a row within a table. The code is

 .Offset(0, 8) = Format(Reg9.Value, "dd/mm/yy")

Everything works fine, the date gets sent to the cell as stated and is all correct. However, I also have vba which runs an advanced filter, which the following code is included in.

            .Range("P7").Value = "=""<""&TODAY()" & "+7"
            .Range("O7").Value = "="">""&TODAY()"

P7 and O7 are the advanced filter criteria. Basically in the form I have a "completion date" which is posted with the first code I posted. So lets say that the date posted is 25/07/14, and I want to have a filter of all the dates which are coming up in the next 7 days to be shown in the Advanced Filter results area of my spreadsheet.

With the second part of code I posted, this results in nothing when using the advance filter. The original posted date in the cell (from the first code) is shown as "25/07/14". So when I run the advanced filter, nothing happens. HOWEVER, if I go to the cell containing "25/07/14", delete the contents and write "25/07/14" manually and then run the advanced filter (second code posted), the date shows up in the results working fine. But I dont understand how me typing it out again makes it work?

Hope I am making myself clear?

Many thanks in advance!

Ash

Originally Posted by Norie

Does the filter work if you use this to put the date in the cell?

 .Offset(0, 8) = DateValue(Reg9.Value)

Yes, yes it does! Awesome.

Thanks very much, do appriciate that!