CODEWITHSUNDEEP
vbaexceldatedatetime
Scott.Flexforce
Scott.Flexforce

Reputation: 57

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

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.

Upvotes: 1

Views: 3272

Answers (1)

Rory
Rory

Reputation: 34055

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.

Upvotes: 3

Related Questions