Date Checking Help Requested...

[cob98tp]

Hi All,

I'm having a little trouble getting some simple VBA code to work, can anybody tell me why?

I think my problem is that when I enter the date in the input box it see it as text, rather than a date... How do I over come this?

Thanks in advance!

Sub Adjust_DR_Data_Custom()


Application.ScreenUpdating = False

StartDateEntry:
    Dim StartDateEntry As Date
    On Error GoTo StartDateEntry
    StartDate = InputBox("Please enter the first day you wish to consider for calculating the Contact Distribution Figures.", "Enter Start Date")
    
        If StartDate > Date - 13 Then
        a = MsgBox("The starting date for the calculations MUST be more than 2 weeks in the past.", vbRetryCancel, "Error")
        If a = 4 Then GoTo StartDateEntry
        If a = 2 Then Exit Sub
    End If
    
    If StartDate <= Date - 13 Then
        GoTo EndDateEntry
    End If

    
    
EndDateEntry:
    Dim EndDate As Date
    On Error GoTo EndDateEntry
    EndDate = InputBox("Please enter the last day you wish to consider for calculating the Contact Distribution Figures.", "Enter Start Date")
    If StartDate > Date Then
        a = MsgBox("The last date for the calculations cannot be in the future.", vbRetryCancel, "Error")
        If a = 4 Then GoTo EndDateEntry
        If a = 2 Then Exit Sub
        End If
    If EndDate <= Date Then
        GoTo ChangeDates
    End If
        
        
ChangeDates:

    Sheets("Contact Ratios").Select
    Range("H1").Select
    ActiveCell.FormulaR1C1 = StartDate
    Range("J1").Select
    ActiveCell.FormulaR1C1 = EndDate
    Sheets("DR Contact Rates").Select


MsgBox ("Statistics Updated.")
    

End Sub

 

[Andrew Poulsom]

You can use VBA's DateValue function to convert a string that looks like a date into a date. Example:

If DateValue(StartDate) > (Date - 13) Then

 

[cob98tp]

Thank you!