• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting out data using a macro if its older than 60 days.

K

Khanzaki

New Member
Hi everyone,

I am badly stuck with this problem need your kind help.

I have a data which consists of date ,invoice number and amount in cell E2 F2 G2 respectively.What I need is a macro that would sort out the data based on value of date in cell E2 (I mean to say that if the date in cell E2 minus todays(current) date or vice versa is greater or equal to 60 days than all data including Date ,Invoice no.and Amount be copied to Sheet2).

I hope it makes sense

Awaiting your replies
 
Before we go writing a macro, you could do this with the Advanced Filter. On Sheet2, in cell A1 input "Date" and in A2 put this formula:

=">="&TODAY()+60


With Sheet2 active, go to Data - Advanced Filter. Select "copy to another location", List Range is Sheet1!E1:G100 (or whatever it really is), Criteria Range is A1:A2, Copy to is E1.


If you still want the macro, I'd recommend just recording the above steps and use that. It's pretty similar to how I'd write the macro from scratch.
 
Hi Luke M thanks for your reply,

But know what I am kinda zero in excel filtering macros and all.

I tried your way and pasted (=">="&TODAY()+60) in A2 but it returns me >=41528 dont know why it happens . I would be glad if you come out with a macro for it ,you know sort of ready made stuff sounds cheap but I need it badly.
 
Hi ,


The issue is not whether a macro is cheap ; the point about a macro in this case is it is overkill.


The steps involved in doing what you want done are so few and so simple , that you would not want a macro to do it , unless doing this is going to be a repetitive task ; some of the links given below even include code to do the job ; all it needs is to understand the facility available within Excel , and use it knowledgeably.


1. http://dailydoseofexcel.com/archives/2012/07/07/excel-advanced-filter/


2. http://blog.compudon.in/2012/05/08/ms-excel-advanced-filter-tutorial/


3. http://chandoo.org/wp/2011/10/10/how-to-use-advanced-filters/


4. http://www.contextures.com/xladvfilter01.html


5. http://www.businessprogrammer.com/201109/excel-2010-advanced-filter/


Narayan
 
You must log in or register to reply here.
Back
Top