Check Date format

[gheyman]

I need to check to see if there are dates and that the data entered into D1 and D2 are actual dates. I used Data validation to check format. I also used the validation to ensure the date in D2 is greater than D1. But I would also like to use VBA as a backup in case someone removes the validation.

1) I want to check that the data in D1 and D2 are "dates" (both fields must have dates). If either one is not, delete the data, format to a date and use the following conditions:
2) If there is a Date in D1 I need to verify that is is not "today". If it is I need to change it to yesterday (Now()-1)
3) If D1 is blank I want to automatically put in a date (January 1, 2000 or 01/012000....)
4) If D2 is blank make it today's date. If its not blank check to make sure its greater then D1. If its not then D1's date plus 360 days.

Thanks for the Help!!!! All this is done on Sheet1

 

[Joe4]

What should trigger the VBA to run?
How are D1 and D2 being populated with data?
There is special VBA called "event procedure VBA", which is VBA code that runs automatically upon some event happening, like data being entered into specific cells.
However, the issue here is if we make it run on the entry of values in D1 or D2, then how do you distinguish between that one of the cells was left blank versus they just haven't gotten to that cell yet?

For example, if you want to check that both cells are populated, if they both start off blank, when they enter something in D1, the code is going to trigger and tell them that they have left cell D2 blank, when they havent't really, they just haven't had the chance to update it yet (you cannot update both D1 and D2 simultaneously, unless you use Copy/Paste and paste to both cells at the same time).

If there is some other event happening that we can use to then check D1 and D2, that might more sense. For example, what happens after they enter data in D1 and D2, do they do something else? If so, maybe we can capture that action, which triggers the code to make sure that D1 and D2 meet all your requirements at that time.

Another option is to control all data entry through a Data Entry User Form, and have VBA validate all the entries when they click the "Submit" button. I don't know if you have ever created any User Forms. It can be a little tricky if you have not done it before, especially if you are not well-versed in VBA. Quite honestly, I find them to be a bit cumbersome, and prefer to use Microsoft Access if I have a project that requires Forms like that. Form creation is so much easier in Access, if you know Access.

 

[gheyman]

It will run by a button on one of the sheets. I am using Power Query. The button triggers the refresh of the power query. The query has date parameters. This is why I want to verify the dates before I run the query

 

[Joe4]

What does the VBA code behind the button that triggers the refresh code look like? Can you post it? I think this is where we want to put the code.

 

[gheyman]

Private Sub CommandButton2_Click()
Sheets("ItemIDPOHistory").Activate

    ActiveWorkbook.RefreshAll
    Sheet2.Range("B2").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    Sheet2.Range("B4").Value = Sheet1.Range("D4").Value
    Sheet2.Range("B5").Value = Sheet1.Range("E4").Value
    
    Sheet6.Range("B2").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    Sheet6.Range("B4").Value = Sheet1.Range("D4").Value
    Sheet6.Range("B5").Value = Sheet1.Range("E4").Value

End Sub

 

[Joe4]

Try this:

Private Sub CommandButton2_Click()

'   First check if D1 is empty or not a date, make it 1/1/2000...
    If (Range("D1") = "") Or (Range("D1") = 0) Or (Not IsDate(Range("D1"))) Then
        Range("D1") = DateSerial(2000, 1, 1)
    Else
'       ...if it is a date, make sure it is not today
        If Range("D1") = Date Then
            Range("D1") = Date - 1
        End If
    End If
    
'   If D2 is empty or not a date, make it today
    If (Range("D2") = "") Or (Range("D2") = 0) Or (Not IsDate(Range("D2"))) Then
        Range("D2") = Date
    End If
    
'   Make sure that D2 is greater than D1
    If Range("D1") >= Range("D2") Then
        Range("D2") = Range("D1") + 360
    End If
    
'   Original code
    Sheets("ItemIDPOHistory").Activate

    ActiveWorkbook.RefreshAll
    Sheet2.Range("B2").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    Sheet2.Range("B4").Value = Sheet1.Range("D4").Value
    Sheet2.Range("B5").Value = Sheet1.Range("E4").Value
    
    Sheet6.Range("B2").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
    Sheet6.Range("B4").Value = Sheet1.Range("D4").Value
    Sheet6.Range("B5").Value = Sheet1.Range("E4").Value

End Sub

Note that I am assuming that the button is on the same sheet as the D1 and D2 values you are checking. If not, put a Sheet....Activate line at the top of the code, activating the sheet where the D1 and D2 value are.

 

[gheyman]

Very much appreciated!