excel vba string to date

Question

Windows 10 Pro, Regional Settings to UK English. In Excel VBA I have a string "02/05/2017 16:30" That, in the UK, means "02 May 2017 16:30"

But VBA turns this to US format somehow and in the cell puts "05/02/2017 16:30"

The VBA code is like this

Dim sField As String
sField = "02/05/2017 16:30"
ws.Cells(1,1) = sField

I can use CDate to get around this but CDate but that requires extra code to determine which cells are dates and which aren't, whereas the implicit conversion works for all types.

Answer 1

Use a Date variable instead, and always provide your date in MDY in VBA.

Dim sField As Date
sField = #05/02/2017 16:30#
ws.Cells(1,1) = sField

AFAIK in VBA you must always work the 'American way', with dates MDY. It does NOT follow regional settings. Which is good, because that enables running the same code on heterogeneous environments.

Answer 2

This is some workaround in the VBA code:

Sub Main()        
    Dim myInput     As String
    Dim splitMe     As Variant
    Dim outputDate  As Date

    myInput = "02/05/2017 16:30"
    splitMe = Split(myInput, "/")        
    outputDate = DateSerial(Left(splitMe(2), 4), splitMe(1), splitMe(0))        
    Debug.Print Format(outputDate, "DD-MMM-YY")
    Debug.Print Format(outputDate, "DD-MM-YYYY")           
End Sub

It takes the date as a string and it splits it by /. Then it takes the year, the month and the day and it builds a new date with the help of DateSerial(). DateSerial MSDN.

In cases like this, make sure that you are passing the correct date to excel and there you may change the format through something as easy as this:

Range("A1").NumberFormat = "m/d/yyyy"

To make sure, that you are passing the correct date, simply try Month(YourDate) over the date or Day(YourDate).

Answer 3

I rather use the built-in VBA functions DateSerial(year, month, day) and TimeSerial(hour, min, sec).

Dim myDateTime as date
mydateTime = DateSerial(2017, 5, 2) + TimeSerial(16, 30, 0)
ws.Cells(1,1) = myDateTime

You can then set the number formatting on the Excel cell to your liking.

I assume this is faster because there is not need to translate any string beforehand. More importantly for me as a programmer, the parameters are explicit. I don't have to worry about different regional setting.

Answer 4

I solved a related problem. My workbook is for use only in the UK. It has a sheet for entering details of cash collected at various venues. The user has two single-cell fields to identify each venue; typically a location and a date, but sometimes the "date" field will contain an extended location name instead. Dates should be entered as dd/mm/yy, but almost anything recognisable is accepted except mm/dd/yy. The details are stored in memory, then later copied to formatted worksheets for printing. I verified the storage in memory. But after the workbook had been in use for a few months, I found that if the user entered a valid date in a cell in the format dd/mm/[yy]yy (e.g. 05/11/17), and its interpretation as mm/dd/[yy]yy would also give a valid date, then the date would obscurely be printed as 11-Mar instead of 05-Nov.

Some code snippets:

'Data structure:
Public Type BkItem             'An item of income, for banking.
    ItemName As String         'The first field, just a text name.
    ItemDate As Date           'The second field, interpreted as a date.
    ItemDateNumber As Long     'The date as internally stored as an integer.
    ItemDateString As String   'Re-formatted string, e.g. "05-Nov-17".
'   ...
End Type    'BkItem.

'Input validation:
BankData = Range(.Cells(BankFirstRow, BankFirstCol), _
                 .Cells(BankLastItemLastRow, BankLastCol))

With BankItem(BankTotalItems)
    .ItemName = IName
    .ItemDateString = BankData(<row>, <col>)
    .ItemDateNumber = DateToLong(.ItemDateString)
End With

'Utility routine. "Paper" is a 2-dimensional array of all the data to be printed
'on one or more pages; "Dest" is a global range.:

Sub OutputDataToSheet(ByVal Size As Long, ByRef CurrentSheet As String, _
                      ByRef Paper() As Variant)
    Worksheets(CurrentSheet).Activate
    Set Dest = Worksheets(CurrentSheet).Range((Cells(1, 1)), _
                                              (Cells(Size, LastCol)))
    Dest.Value = Paper 'Copy data to final sheet for printing.                                         
End Sub    'OutputDataToSheet.

'As we build the array "Paper", it helps to format those cells on the final
'printout worksheet which are going to contain dates.

.Range(Cells(CurRow, L15c01), Cells(CurRow, L15c01)).NumberFormat = "dd-Mmm-yyyy"
'For the item date.
.Range(Cells(CurRow, L15c01), Cells(CurRow, L15c01)).HorizontalAlignment = xlCenter

If IsDate(BankItem(item).ItemDateString) Then
    Paper(<row>, <col>) = BankItem(item).ItemDateNumber
    'Date as a number, so OutputDataToSheet preserves UK date format.
Else
    Paper(<row>, <col>) = BankItem(item).ItemDateString
    'Extension of name.
End If  'IsDate(.ItemDateString).