VBA Userform Date/Time Stamp - UK Date changes to US format when copied to cell

Question

I have a userform that has a date and time stamp dd/mm/yyyy hh:mm:ss.

It shows in UK format in the text box, and I generate it using this code:

Dim iNow
Dim d(1 To 6)
Dim i As Integer


iNow = Now
d(3) = Year(iNow)
d(2) = Month(iNow) & "/"
d(1) = Day(iNow) & "/"
d(4) = Hour(iNow) & ":"
d(5) = Minute(iNow) & ":"
d(6) = Second(iNow)

For i = 1 To 6
  If d(i) < 10 Then Timestamp = Timestamp & "0"
  Timestamp = Timestamp & d(i)
  If i = 3 Then Timestamp = Timestamp & " "

Next i
datetextbox.Value = Timestamp

The issue occurs when the following block of code transfers this information to the Excel database sheet using the following code:

'Determine emptyRow in Database Sheet
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 14).Value = datetextbox.Value

What happens is the date changes to MM/DD/YYYY when it enters into the spreadsheet cell. I have tried number formatting in VBA and also on the drop down menu and used custom settings, and checked that the regional settings are UK on two separate machines. This is an integral part to a document I have created, that will recognise the most recent record of the same reference number using this formula which then transfers data to a cleansing sheet:

=MAX(IF(Database!$P$2:$P$1437=P488,IF(Database!$P$2:$P$1437=P488,Database!$N$2:$N$1437,"")))

I did try a simple Now() stating the format dd/mm/yyyy/ hh:mm:ss but this also converts to US format.

Any help would be appreciated.

Note that you could also use Timestamp = Format(Now,"DD/MM/YYYY hh:mm:ss"). If your regional format uses something other than / as a divider you need to use an additional "\": "DD\/MM\/YYYY". Timestamp should probably be Dimed as String so you don't end up with a Date type, — arcadeprecinct

Rory Rory · Accepted Answer · 2016-07-05T08:54:53

I suspect this has been answered many times before on here, but you need to use CDate:

Cells(emptyRow, 14).Value = CDate(datetextbox.Value)

This will convert the date string to a true date value using your regional settings.

VBA Userform Date/Time Stamp - UK Date changes to US format when copied to cell

1 Answers