VBA Userform Date/Time Stamp - UK Date changes to US format when copied to cell
<p>I have a userform that has a date and time stamp dd/mm/yyyy hh:mm:ss.</p>

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

<pre><code>Dim iNow
Dim d(1 To 6)
Dim i As Integer


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

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

Next i
datetextbox.Value = Timestamp
</code></pre>

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

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

<p>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:</p>

<pre><code>=MAX(IF(Database!$P$2:$P$1437=P488,IF(Database!$P$2:$P$1437=P488,Database!$N$2:$N$1437,"")))
</code></pre>

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

<p>Any help would be appreciated.</p>