extract dates from column

[wingfield65]

hello folks,

I have a column of data that has mostly dates in it. however, some rows have dates with the format "09/02/2014 as of 09/01/2014". all other rows have a normal date in format "09/12/2014". I'd like to run through all the rows and check for the odd format and change it so that it reflects the first date shown. for example, the odd format "09/02/2014 as of 09/01/2014" would become "09/02/2014". all other column entries would have no change. this would be in VBA. I'm thinking some type of loop that uses the frequently seen LR variable to determine how many rows there are and some algorithm to correct the dates.

thoughts?

thank you for the help.

[protonLeah]

=iferror(datevalue(left(a1,10)),a1)

[fredlo2010]

Hi,

Maybe a formula can do the job. Take a look at the formula I put together. I am using the space before " as of" as the guide to retrieve the first date. The formula is pointing to column A.

Please Login or Register  to view this content.

Thanks

[wingfield65]

fred and leah,

the formulas work; but how would I get them into VBA?

thank you

[fredlo2010]

If you want the transformation to be done in VBA. A formula will be a faster solution.

Please Login or Register  to view this content.

Thanks

[wingfield65]

ok folks. figured it out. I just used record macro, copied the formula down and that worked. thank you for all the help.