How to filter an excel columns which contain date/time as string

Question

I have an excel sheet to filter a Column. The column relates to total experience of a person. The values are like 5years 2Months, 32Years 6Months etc... all the values are in String format. I need the following functionality.

• 1. when i enter >5 in a textbox(which i will create in a form), it should display only experience which are less than 5(filtering) . I need an idea how to do this in vba.

Can anyone help..? I just need a way to do this.

Answer 1

Consider the following screenshot. Column a has the unfortunate text with years and months.

Column B splits out the years. Column C splits out the months. Column D has the total number of months for the time frame. With this in place, you can filter by any of the columns using the filter options of the Autofilter built into an Excel table.

The formulas are as follows:

Years: =MID([@total],1,FIND("Years",[@total])-1)+0

Months: =MID(SUBSTITUTE([@total],"Months",""),FIND(" ",[@total])+1,99)+0

Duration in months: =([@years]*12)+[@months]

Now just use the filters in the drop down butttons of the column headers and there is no need for VBA at all.