Date format VBA (versus Date format in Excel sheet)

Question

I want to use a date (which is specified in a cell F2 = 01/11/2022) in a filter:

ActiveSheet.ListObjects("QuotationOverview").Range.AutoFilter Field:=5, Operator:= xlFilterValues, Criteria2:=Array(1, "11-2022")

As you can see I now have the date (month), hard coded "11-2022" but if I change that into Range("F2") the sorting is applied on January 2022. The VBA code expects the format mm/dd/YYYY but the format in Excel is dd/mm/YYYY.

ActiveSheet.ListObjects("QuotationOverview").Range.AutoFilter Field:=5, Operator:= xlFilterValues, Criteria2:=Array(1, Range("F2"))

How can I inform VBA that the format is dd/mm/YYYY and not mm/dd/YYYY?

11-2022 isn't a date because it lacks a day of month. Dates have no format either in Excel or VBA, they're binary values. Formats apply only when parsing text into a date, or formatting a date into text. When you enter a date into a cell it's actually saved as an OLE Automation Date (OADate) which is essentially a float whose integral part is an offset sinse 1899-12-31 and the fractional part represents the time of day — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 2, 2022 at 8:46
When you select a Mont/Year style for a cell, Excel still saves a full date, eg 2022-10-01 but displays only the month and year. That's what allows date functions to work with that value. In VBA you can construct a date with DateSerial(2022,10,1) and pass it as an argument anywhere you want to use a date. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 2, 2022 at 8:49
If Criteria2 expects strings though, you still have to specify a full date, ie including the date, using the US date format. This will be translated into a VBA Date value, no matter the user's locale or the cell style of individual cells — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 2, 2022 at 8:54

Gustav · Accepted Answer · 2022-11-02 10:18:52Z

0

Try using Format to generate your month-year text values:

ActiveSheet.ListObjects("QuotationOverview").Range.AutoFilter Field:=5, Operator:= xlFilterValues, Criteria2:=Array(1, Format(Range("F2"),"mm-yyyy"))

answered Nov 2, 2022 at 10:18

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date format VBA (versus Date format in Excel sheet)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related