Date Checking Help Requested...

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
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!

Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

Forum statistics

Threads
1,141,097
Messages
5,704,313
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top