Working with Dates, VB MAcro to Auto Filter on last 7 days

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:

Please Login or Register  to view this content.

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

Put the date on a separate line
Untested

Please Login or Register  to view this content.

AB33, probably being thick but what do you mean when you say put the date on a separate line?

Interestingly enough, this doesn;t work when the criterial is set to ">mydate" but if i flip it to "<mydate" it shows the entries where the dates have text appended to them...

confused... i know it doesn't take much!? Help, please?

Does the filter work if you use "<mydate?.
Have you tried to format the data "28/09/2012 08:59:54" in to date format using Excel's formatting function?
I think the issue is formatting. The code will not find match until both dates are the same.

I think your Mydate has to be outside of the " " like you have in your original code. Use Ab33's code and try with this on your last line:

Please Login or Register  to view this content.

I think your Mydate has to be outside of the " " like you have in your original code. Use Ab33's code and try with this on your last line:

Please Login or Register  to view this content.

oops, sorry for the double-posting!

djdjdj,
Thanks! I did not test it.
Despite all this the code will not work until the data are converted in to dates

okay.... so interestingly.... i've spent weeks trying to get my data to import correctly from a csv so it reads into columns in the correct date format DD/MM/YYYY and so that it doesn't do stupis stuff with text dates etc... won't bore you with it, but finally cracked it!

anyway hovering over the autofilter icon after the filter has been applied and its showing that "StatusUpdated Date applied After :06/12/2012" which is technically correct in the US date format! Its DATE-7!!!

Any ideas on how to get DATE to be DD/MM/YYYY?! if i can crack that then the chances are the filter might work, as it looks like the formatting is ok?

It does not matter how the dates appear, be it USA, or British dates as long as they are dates.

But if you want to format, let's column B

Please Login or Register  to view this content.

i'm not overly worried about formatting them - they are formatted and it doens't make a difference on being able to filter them...

i've attached a sample of the spreadhseet to try and illustrate what I mean, I'm sure its something to do with having the DD and the MM round the wrong way, but others seem to disagree, keen to hear if anyone else has encountered this or similar

Thanks

The code works on the sample. You need to change the column date.

AB33, i must be missing something.. or being really thick, The code

Please Login or Register  to view this content.

runs fine, but doesn't filter correctly on either column C or column G of the data - Both columns have dates that are < than > than todays date -7days so i would expect them to filter accordingly but they don't!

I don't get what i'm doing wrong, clearly something.

Please follow my code. I have written you a new code with new variable names. If you look at my code, before it filters, it converts the column with the date in to text format, it then filters that column. To prove my code works, I have copied the filtered data in to sheet 2 as you can see on the attached.
I thought the issue you have is the date format. The code does not compare anything, but merely filters a column with a criteria.

Cheers... so i was being a bit thick! Totally missed the code, I'm assuming its this..

Please Login or Register  to view this content.

But I still don't think its' working or doing as I would expect... maybe it's me..


But I would expect that it only shows the results that fall within the last 7 days i.e from 13/06/2013 - 20/06/2013, but when the code runs this is not the case.... its showing results from May 2013 Oct 2012 and Sept 2012.

also its converting the values in column G (the other date column) to the DATEVALUE and filling down the whole of column C with a date of 00/01/1900 before it does the cut copy...

Am I losing it?

AB33 - apologies - i'm with you ... i just need to get my head round working with your code and some of the other stuff I have to get it working to do what I need it to! DOH!!

Am I losing it?
No, I am.
Dates are nightmare to deal with in excel.
Hope, others will butt in.

you and me both! this is proving trickier than it should!!