Date Ranges won't group in filter

jvalente · Jul 4th 2016

Hi All,

I have a unique problem that usually a text to columns macro fixes as per below.

Code

Columns("D:D").Select
    Selection.texttocolumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

My problem is as below (please refer to problem jpg). The group dates won't group with the other dates.

I've tried everything from copy and paste special as values

Then changing the date format to

Code

.NumberFormat = "dd/mm/yyyy"

But nothing[ATTACH=CONFIG]69595[/ATTACH]

This is super frustrating.

**Ger Plante** · Jul 4th 2016

Re: Date Ranges won't group in filter

Without seeing your source data I would imagine that the dates are being stored as text. Trying multiplying the dates by 1, or adding zero to convert it to a number (use .pastespecial & multiply), and then apply your number format as above.

Ger

jvalente · Jul 5th 2016

Re: Date Ranges won't group in filter

Yeah they are, which is why I tried using a text to columns method. This works when doing it manually, but after recording it and using that, it doesn't want to budge oddly enough.

I'll give your method a go.

**Ger Plante** · Jul 5th 2016

Re: Date Ranges won't group in filter

Also, make sure there is no single apostrophe (') in the cell before the text date.

If that doesnt work, there are about 10 different methods, with examples on how to convert text to dates in this page here... its as good as I have seen.

https://www.ablebits.com/offic…/excel-convert-text-date/

They cover off pastespecial with addition (my suggestion above) and also text to columns (which is what you were trying).

Ger

jvalente · Jul 5th 2016

Re: Date Ranges won't group in filter

The multiply by 1 worked a treat.

Thanks!

Date Ranges won't group in filter

Participate now!

Share

Users Viewing This Thread