I have a spreadhseet that has 2 columns in it, both containing dates:

the 1st column has dates in the format: "28/09/2012 08:59:54" and is set to Custom in Excel

the 2nd column has some dates populated and some blank - those populated are set as 06/06/2013 and set as Date *DD/MM/YYYY and some have been amended and contain text at the end of them and have imported like this 11/06/2013PENDINGREVIEW and are in General format. These are few and far betweeen, but exist infrequently across 20 worksheets contained in the workbook!

I need to be able to use an auto filter to show the lines recieved within the last x number of days, on both of these columns to allow me to Count on these dates and then perform measurements against the two columns - one is basically a date recieved and the other is a date an action was completed!

I had used the following previously but i'm guessing due to the formatting issues and the fact that some of them contain text etc then the autofilter its not working:

Sub Last7_Days()
'
' Last7_Days Macro
' Cuts data to show only last 7 days
    Range("N1", Range("N" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=">=" & (Date - 7)

End Sub
Has anyone got any suggestions for:

1) cleaning up the cells that contain text appended to the dates - other that educating users not to add text - tried that and its working to an extent!
2) correctly formatting the columns to allow the filters to work
3) allowing an Input variable on a control sheet to change the number of days that the autofilter operates on...
4) coding the macro for the autofilter to work across the 20 worksheets

Asking alot - i'm going to continue to plug away at it, any input greatly apprecaited... thanks