Simple VBA Code with Dates

Submersed

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
365
Platform
Windows
Hi All,

Got a major headache at the minute and can't for the life of me think straight!!

I am trying to say:

If Column A (Date) is within 2 months of todays date then

Any help would be appreciated...im off to find tablets!

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
What problem are you having?

If we assume 60 days for 2 months, then this is OK

=IF(AND(NOW()-60<=a3,NOW()+60>=a3),"True","False")

or is it working out the 2 months either side of now ?
 

Submersed

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
365
Platform
Windows
What problem are you having?

If we assume 60 days for 2 months, then this is OK

=IF(AND(NOW()-60<=a3,NOW()+60>=a3),"True","False")

or is it working out the 2 months either side of now ?

Sorry, should of explained, I am trying to do it within VBA....
 

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Here's a formula:

=IF(AND(A3>=NOW()-60,A3<=NOW()+60),"Yes","No")

If you want to do VBA code:

Code:
Dim i As Integer
    For i = 1 To 10
        If Range("A" & i).Value >= Now() - 60 And Range("A" & i).Value <= Now() + 60 Then
            MsgBox Range("A" & i).Address & " is within 60 days of today."
        End If
    Next i
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Try :-
Code:
[COLOR="Navy"]Sub[/COLOR] MG04Aug56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]If[/COLOR] Dn > DateAdd("m", -2, Date) And Dn < DateAdd("m", 2, Date) [COLOR="Navy"]Then[/COLOR]
    Dn.Interior.ColorIndex = 6
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Submersed

Board Regular
Joined
Jul 6, 2007
Messages
167
Office Version
365
Platform
Windows
Thats perfect guys! Nice one!

Thanks a lot!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,494
Messages
5,511,658
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top