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.
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 beDim
ed asString
so you don't end up with aDate
type, – arcadeprecinct