Store Date in DD/MM/YYYY in Excel & Compute Date Difference

Question

I have a Excel Sheet with below columns .

 A            B                   C                 D
 Worker       Work Start Date     Work End Date     Working Period(B-C)
 1            1/1/2014            1/10/2014         0 Years 0 Months 10 Days
 2            1/1/2014            2/28/2014         0 Years 2 Months 00 Days

I have Userform that will accept these values from the textbox and store in Excel sheet.

Problem is When is input Dates[in dd/mm/yyyy format] as 15/07/2014 it accepts and save it as 15/07/2014 in DD/MM/YYYY format in Excel Sheet .

If i give 15/17/2014 it throws error Date should be in DD/MM/YYYY

Same thing If i give Date as 07/15/2014 it accepts the date and in Excel column it saves as 7/15/2014 while it displays 15/07/2014 since i have set format as DD/MM/YYYY

I used below validation .

If Me.txtStartDate.Value = "" Then
    MsgBox "Please enter Work Start Date."
    Me.txtStartDate.SetFocus
    Exit Sub
End If

If Not IsDate(Me.txtStartDate.Value) Then
    MsgBox "The Work Start Date Field must be in DD/MM/YYYY format"
    Me.txtStartDate.SetFocus
    Exit Sub
End If

Ideally this is what i am expecting. Only valid date should be entered via Form. I use textbox to input date in DD/MM/YYYY Format. There are chances that user may enter 01/12/2014 or 1/12/2014 in dates. I mean leading zeros may be entered or not for DD & MM.

Please advice how i can handle this. How excel treats dates to store actually in which format .

How to calculate Period column in Years Months Days Format.

Thanks for the help .

Answer 1

I think this function should be suitable to check the string in your textbox for correctness. If it is ok it returns true and the parsed date in the second parameter.

Option Base 0

Function GetDate(str As String, ByRef res As Date) As Boolean
    On Error GoTo Err

    Splits = Split(str, "/")
    If UBound(Splits) + 1 <> 3 Then
        Err.Raise 0
    End If

    ds = DateSerial(Splits(2), Splits(1), Splits(0))

    ' e.g. DateSerial(2014, 1, 32) would be interpreted as Feb 1, 2014; this is checked here
    If Month(ds) <> CInt(Splits(1)) Or Day(ds) <> CInt(Splits(0)) Then
        Err.Raise 0
    End If

    res = ds
    GetDate = True
    Exit Function
Err:
    GetDate = False
End Function

The problem is that you can define the output format but VBA parses input according to the regional settings on your computer. If your desired format differs from the default format you have to implement the parsing yourself.

Answer 2

couple of things:

a. you can use in appropriate place inside your code (to bypass validation check code)

With ActiveWorkbook.Worksheets("yourSheet") 
.Columns("B:B").NumberFormat = "mm/dd/yyyy;@"  
.Columns("C:C").NumberFormat = "mm/dd/yyyy;@"  
End With

b. you can use datediff like this:

ToDate = "10/09/2014"                  'use range("yourRange").value  
FromDate = "09/19/2014"                'use range("yourRange").value   

dayDiffered = DateDiff("d", ToDate, FromDate)
monthDiffered = DateDiff("m", ToDate, FromDate)
yearDiffered = DateDiff("yyyy", ToDate, FromDate)