Auto filter with date (UK format) array issue

Question

I am trying to auto filter datasets pertaining to a set date array which are stored in the UK format - for ex: 11/04/2019 (April 11, 2019), 15/04/2019 (April 15, 2019). The macro is converting the UK date format to US format, however, it is filtering only those dates which are under 12 and is discarding those above 12. The code I used is shown below:

i = 0
For Each iCell In Control.Range(Cells(5, 3), Cells(99, 3))
    If iCell.Value <> "" Then
    'CONVERT DATE TO US FORMAT FOR VBA
        iDte = Format(iCell, "mm/dd/yyyy")
        i = i + 2
        ReDim Preserve DteAry(1 To i)
        DteAry(i - 1) = 2
        DteAry(i) = iDte
    End If
Next iCell

[Dates to be filtered][1]

I've found use of a function like this one to split up the day, month and year and put it all together can help when experiencing region based issues with date formats. — Samuel Everson
– Samuel Everson, Commented May 9, 2021 at 12:58

Gustav · Accepted Answer · 2021-05-09 09:06:07Z

0

If you store true date values, not text, these carry no format, so:

    iDte = iCell
    i = i + 2
    ReDim Preserve DteAry(1 To i)
    DteAry(i - 1) = 2
    DteAry(i) = iDte

answered May 9, 2021 at 9:06

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

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

6 Comments

TK11 Over a year ago

Thanks for getting back. I tried the code you suggested, however, it is not able to filter anything and is returning empty values. The date values in the array are stored as true date values with the date format, and the raw data from which I am trying to filter out has them in custom format, such as 11/04/2019 07:00:00 etc., Any other advice pls?

Gustav Over a year ago

True date values carry no format, thus no format is "stored" within the array. So, it is unclear what you are doing. For comparing date values, use DateDiff.

TK11 Over a year ago

Is it possible for me to send a sample macro for your perusal?

Gustav Over a year ago

Yes, add it to your question for everyone to study.

TK11 Over a year ago

Thanks, I have included a link to the macro and a sample file as suggested.

|

Collectives™ on Stack Overflow

Auto filter with date (UK format) array issue

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related