Excel date format and value inconsistency

Question

I am trying to understand how Excel works with date formats and date values.

context of the problem:

My application has the following architecture

An Excel VBA client: 2016 French with windows 7 French
An SQL server data base

In the Excel workbook a sheet has a Change event handler used to capture data (for the simplicity of the explanation let's suppose there are all dates) and save the input value into the database. Values are captured and saved as strings and loaded as text and formatted as dates once in the sheet.

In the sheet the Change event handler processes the input which is traced with the following code (event handler is disabled when loading the data from the database).

With Target 
    Dim mydate As Date
    mydate = CDate(.Value)
    Debug.Print " day = " & Day(mydate) & ", month = " & Month(mydate)
End with

When a format is set manually (right click in a cell > format cell > date > select or define the format you wish) months and days are interpreted as expected.

Having set a validation on the input if I try to input a date such as 10/23/2018 Excel throws an error (I have a French operating system and a French Excel version). In this example the first item in a date is day (10) followed by month (23, not valid for this example) and finally year (2018).

Sub SetValidation(ByVal dataRange as Range)

With dataRange
    With .Validation
    On Error GoTo ErrorHandler
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
            Operator:=xlGreater, Formula1:="1/1/1900"
        .InCellDropdown = True
        .IgnoreBlank = True
        .ErrorTitle = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With '.Validation
End With 'dataRange
ErrorExit:
Exit Sub
    ' process error
    GoTo ErrorExit
End Sub

When I type 23/10/2018 the input is accepted and saved as typed (as a string).

Now when the input is 04/05/2018, for instance, the date is potentially ambiguous depending on whether the environment of the user reading it is French or Anglo-Saxon.

So I want to make it clear what was typed using an explicit format with the month displayed as a string.

If I set a format as "dd mmm yyyy" the value is displayed as 04 mai 2018 (day =04, month= may) and the trace in the Change event handler outputs the following

day = 4, month = 5 which is consistent with what is displayed.

Now when the exact same format is applied using VBA code it does not provide the same output - day and month are swapped.

With dataRange
    .NumberFormat = "dd mmm yyyy"
    .Value = .Value 'force format to update 
end With

To make it clear:

VBA loads a date string as "04/05/2018"
VBA applies the format "dd mmm yyyy" and the date is formatted as "5 apr 2018"
A user types in "04/05/2018" in a blank cell with the same format and the date is formatted as "4 may 2018"

The problem does not concern how to set a validation for a date which on a french system does not use the anglo saxon convention: when typing a date like 03/23/2018 surprisingly the validation throws an error no matter how the value is rendered.

The problem is about the inconsistency on how a date is represented internally and how it is rendered to the user when applying a format using VBA. I would expect that applying a format manually would give the same result as applying it via VBA.

when the date 04/05/2018 is input it is validated but applying the format in VBA the value becomes 05/04/2018.

In both cases the format instruction is exactly the same.

I'd like to understand why the VBA code and the Excel user interface have different behavior and if a fix would be available.

EvR EvR · Accepted Answer · 2018-11-21T22:51:19

VBA uses (always) US date formatting, does the help of DateValue (see below) work for you?

Sub SetValidation(ByVal dataRange as Range)

With dataRange
    With .Validation
    On Error GoTo ErrorHandler
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
            Operator:=xlGreater, Formula1:=DateValue("04/05/2018")
        .InCellDropdown = True
        .IgnoreBlank = True
        .ErrorTitle = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With '.Validation
End With 'dataRange
ErrorExit:
Exit Sub
    ' process error
    GoTo ErrorExit
End Sub

Excel date format and value inconsistency

1 Answers