Excel vba string to date

Question

Windows 10 Pro, regional settings in English English. In Excel VBA, I have the line "05/02/2017 16:30" This means that in the UK it means "May 02, 2017 16:30"

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

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 it requires extra code to determine which cells are dates and which are not, while implicit conversion works for all types.

Answer 1

Instead, use the Date variable and always specify the date in MDY in VBA.

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

AFAIK in VBA you should always work "American" with MDY dates. It does NOT match regional settings. This is good because it allows you to run the same code in heterogeneous environments.

Answer 2

This is a temporary solution in 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 breaks it into / . Then it takes the year, month, and day and builds a new date using DateSerial() . DateSerial MSDN .

In such cases, make sure that you pass the correct date in order to succeed, and there you can change the format using something as simple as this:

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

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

Answer 3

I solved the related problem. My book is for use in the UK only. It has a sheet for entering information on cash collected at various sites. The user has two fields with one cell to identify each place; usually location and date, but sometimes the date field will contain the extended name of the place. Dates should be entered as dd / mm / yy, but almost all recognizable ones are accepted except mm / dd / yy. Parts are stored in memory and then copied to formatted worksheets for printing. I checked the memory in memory. But after the book was used for several months, I found that if the user entered a valid date in the cell in the format dd / mm / [yy] yy (for example, 05/11/17) and its interpretation as mm / dd / [yy] yy will also give a valid date, then the date will be obscurely printed as March 11th and not November 05th.

Some snippets of code:

 '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, eg "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). 

Answer 4

I rather use the built-in functions VBA 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 

Then you can set the number formatting in the Excel cell as you like.

I guess this is faster because there is no need to translate any line in advance. More importantly for me as a programmer, the parameters are explicit. I do not need to worry about different regional settings.