Compare date in a cell to TODAY and have msgbox if date is in the past

[Mikeymike_W]

Hi All,

I have a cell in my sheet where the user will insert a date.
If this date is in the past i would like a msgbox to open and inform them of this.

I feel like this should be really easy but i just can get it working.

Thanks in advance for any help,

Mike

 

[Rick Rothstein]

I have a cell in my sheet where the user will insert a date.
If this date is in the past i would like a msgbox to open and inform them of this.

When you ask a question, you should try to be specific. Assuming the "cell in my sheet where the user will insert a date" is cell A1, give this event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If IsDate(Target.Value) Then
      If Target.Value < Date Then MsgBox "That date is in the past!"
      Target.Select
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Fishboy]

Hi All,

I have a cell in my sheet where the user will insert a date.
If this date is in the past i would like a msgbox to open and inform them of this.

I feel like this should be really easy but i just can get it working.

Thanks in advance for any help,

Mike

Hi Mike,

Without knowing which cell you are wanting the users to complete I have had to make a guess at cell A1. So basically, the following Worksheet_Change macro will check cell A1 when it is updated and if it is a date AND that date has passed then it will display an error message:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If IsDate(Target.Value) Then
            If Now > Target.Value Then
                MsgBox "Date has already passed"
            End If
        End If
    End If
End Sub

 

[Mikeymike_W]

Hi Rick, Hi Fishboy,

Thanks very much for your respoinses and sorry for my vagueness.

I tried both pieces of code and they both seem to work as well as the other.

Many thanks for all your help,

Mike

 

[Fishboy]

Hi Rick, Hi Fishboy,

Thanks very much for your respoinses and sorry for my vagueness.

I tried both pieces of code and they both seem to work as well as the other.

Many thanks for all your help,

Mike

You're welcome. Happy to help.

 

[Mikeymike_W]

Hi,

Can i ask one more thing please.

How would i alter the code so that it allows for 4 months?

So if the date entered is more than 4 months from todays date then the msgbox will appear?

Thanks in advance,

Mike

 

[Rick Rothstein]

How would i alter the code so that it allows for 4 months?

So if the date entered is more than 4 months from todays date then the msgbox will appear?

I presume you mean more than 4 months in the past from today's date. Here is my code modified to do that...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If IsDate(Target.Value) Then
      If Target.Value < DateAdd("m", -4, Date) Then MsgBox "That date is in the past!"
      Target.Select
    End If
  End If
End Sub[/td]
[/tr]
[/table]

 

[Mikeymike_W]

Hi Rick,

Sorry for the slow response.

Your code worked a treat.

Thanks very much for your help