filter text column containing date

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
135
Office Version
  1. 365
Platform
  1. Windows
how do i using vba filter a row of data containing dates.

When i set a breakpoint the filter is showing a text string of < 43961 which was converted from code below. If i mouse over the oPDT field it shows a date of "5/10/2020"

Dim myDate as Date
Dim oPDT As String

myDate = "06/09/2020"
oPDT = Format(DateAdd("d", -30, myDate), "MM/DD/YYYY")

.AutoFilter Field:=7, Criteria1:="<" & oPDT

data in column 7
01/01/2019
06/01/2020
02/03/2018

after filtering i would expect to find the 01/01/2019 and 02/03/2018 but nothing is showing up

Thanks
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,438
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe the below, set the range to suit

VBA Code:
    Dim myDate As Date
    Dim oPDT As Long

    myDate = DateValue("06/09/2020")
    oPDT = DateAdd("d", -30, myDate)
    With Range("A1").CurrentRegion
        .AutoFilter Field:=7, Criteria1:="<" & CLng(oPDT)
    End With
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply.


let me go after this a different way. Below is a snipit of my data found in column "G".

It show two blank rows,
1 heading,
1 blank row,
1 date,
1 blank row,
1 date,
1 blank row and then
1 date


This entire column is text

how could i code something in VBA that would change those found that contain text(datevalues) to date(datevalues)?

Thanks


Release Date
06/04/2020
06/04/2020
06/04/2020
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,438
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try selecting the column, click Data - Text to Columns, click next, check that there is nothing in the Other box and click next, in the Date section select the date format that your data is in and click finish.

Then test the code that I posted.
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
135
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the prompt response.

I have over 40 files being opened and cycled one at a time. The vba code always worked until the input sheets changed that column from date field to a text field. I was hoping to somehow to just change that text field back to a date for the whole column and then process. Sorta looks like im striking out.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,438
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Did you try what I put in my last post? you can just record your actions if you want it in a macro.
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
135
Office Version
  1. 365
Platform
  1. Windows
yes, i did that yesterday and found that I would have to modify the macro created to handle variable sheets as each sheet being read in is somewhat different.
I was hoping for a better solution. (Still don't know all the slick ways of doing things)

I did try something that is a bit kludgy a few minutes ago.

In my code

inserted a new column "A"
inserted a formula =IFERROR(DATEVALUE(H1),0) into "A" and copied down
changed my Autofilter to used this column

then after the filter was executed -
deleted the entire column "A"

Not very pretty but it might do the job

Thanks for the help
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,438
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
macro created to handle variable sheets as each sheet being read in is somewhat different.
Rather than what is different about the sheets what is the same? for instance the column header names?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,492
Messages
5,548,362
Members
410,828
Latest member
A9Bosv3
Top