VBA code for cell date is less than current date + 2months

**craigten** · 05-07-2013, 07:22 AM

Hello,
Can someone please help me with a line of code to say: "if the cell date is less than todays date +2months then..."

I need it to to replace this line of code:

Please Login or Register  to view this content.

used in this part of the code:

Please Login or Register  to view this content.

Thanks

Craig

**AndyLitch** · 05-07-2013, 07:26 AM

Please Login or Register  to view this content.

**craigten** · 05-07-2013, 07:58 AM

Hi AndyLitch
I put the line of code into my code as below:

Please Login or Register  to view this content.

but it also collects dates outside "date +2 months" maybe i need to include more of your code, any suggestions?

Thanks

Craig

**AndyLitch** · 05-07-2013, 08:12 AM

Hmmm ... It's a bit weird... I tried it and it was only adding a month to mine

Try this

Please Login or Register  to view this content.

and put a messagebox in it temporarily so you can see what it's checking for

Please Login or Register  to view this content.

**craigten** · 05-07-2013, 08:45 AM

hi Andy,
No good, that code doesnt seem to extract dates within the 2 month window (today +2month). I have lists of info on several sheets and the macro loops through them all to extract lines of info containing an expiry date which is less than the current date +2month. It worked well when I used code extracting dates which I had manually changed to red font, but this is very tedious and takes a long time... Any other ideas ?

Thanks

Craig

**AndyLitch** · 05-07-2013, 08:58 AM

That dateserial appears to be rubbish.... If 60 days is any good you could use

If Sheet3.Cells(Y, 2).Value < Int(Now()) + 60 Then

as an inetrim measure and I'll look deeper into it

**craigten** · 05-07-2013, 09:12 AM

Andy
I Just tried

Please Login or Register  to view this content.

it appears to work, ill do some double checking let you know

Thanks

**JasperD** · 05-07-2013, 09:17 AM

Try this :

Please Login or Register  to view this content.

Worked for me, but my EXCEL date formatting is different than yours, so let me know if it works, please :-)

**craigten** · 05-07-2013, 09:18 AM

Hi Andy

Yes, it is working, thanks for the guidance

Regards

Craig

**JasperD** · 05-07-2013, 09:20 AM

Hi Craigten : Andy's solution is "Today + 60 days", while mine is "Today + 2 months" ; this of course differs - perhaps try both solutions?

**craigten** · 05-07-2013, 09:21 AM

Sorry Jasper, wasnt aware you had joined in, I tried your code but it doesnt work (run time error), ill stick with the simple code in post #7, seems to work ok, ill do some testing with it.. 60 days / 2months its just a warning period for me to act so +/- a few days doesnt really matter too much.
Not sure why yours didnt work with my code, code in post #7 seems easier unless it gives me an error later on, hope not, but at least i have something now to work with..

Thanks guys

Craig

VBA code for cell date is less than current date + 2months

LinkBack

Thread Tools

Rate This Thread

Display

VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Re: VBA code for cell date is less than current date + 2months

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions