Date Problem

[gibbo1715]

All

I am using the code below to autofilter a date

Can anyone tell me why this still formats my date in the american format and not the uk ?

Cheers

Gibbo

[VBA] Workbooks("Outstanding").Sheets("Data").Range("B8:L" & iLastRow).AutoFilter _
Field:=10, Criteria1:=">=" & Format(txtInputFrom.Text, "dd/mm/yyyy") [/VBA]

I ve tried swapping my format around to mm/dd/yyyy and that works for me but was wondering is this the only way to do this or is there a better way?

[matthewspatrick]

Gibbo,

Can you try formatting as dd-mmm-yyyy, or yyyy-mm-dd?

[Bob Phillips]

All

I am using the code below to autofilter a date

Can anyone tell me why this still formats my date in the american format and not the uk ?

I am not clear Gibbo. What exactly is being formatted? As I see it, you are filtering by date. Are you saying that UK style dates in your list appear as US style dates after filtering?

[gibbo1715]

Hi Bob

Yes, what im doing is via my user form invoking the auto filter on a different workbook and then filtering the date field and copying to a new workbook.

My textbox input is in the format dd/mm/yyyy

When i check my data the custom field on the autofilter swapps the dd/mm to mm/dd, i have found by formatting my text box as mm/dd/yyyy in my code solves the problem.

I was just wondering why it does that, my excel and PC are set up for UK dates?

Cheers

Gibbo

[Bob Phillips]

Hi Bob

Yes, what im doing is via my user form invoking the auto filter on a different workbook and then filtering the date field and copying to a new workbook.

My textbox input is in the format dd/mm/yyyy

When i check my data the custom field on the autofilter swapps the dd/mm to mm/dd, i have found by formatting my text box as mm/dd/yyyy in my code solves the problem.

I was just wondering why it does that, my excel and PC are set up for UK dates?

Cheers

Gibbo

Gibbo,

The fundamental problem is that VBA talks US style dates, regardless of whatever your settings may say.

Formatting is US style is one solution, another maybe to comapre the range to

Str(CDbl(TxtInputFrom.Text))