VBA Code (date format) should correspond with custom dateformat

Good morning

I have been trying for three weeks now to get this right but after watching videos and trying everything cant seem to get it to work.

I have a file that when you type things in certain cells, it automatically creates a date stamp in cell "I"

I then have a macro to copy the fields to another file that must be opened (I use a macro for that)

then the file where everything copied must be saved with the new information and then uploaded to a website.

the problem is that when it copies, the date changes to a number. I did watch various videos to make it correct but does not seem to work.

So I am uploading three small excel files.

Book2.xlsm is the file where you type in anything then it will generate a date stamp in cell "I"
Results.xls (and it must not be a macro enabled file) is the one that must be uploaded. there is a macro on the sheet when u click it will copy the info over.

make sure Results.xls is open before running the macro.

When you copy the results over, it overrides the values and format in cell "I' and then the date shows as a number which gives an sql error when I upload it to a specific website.

I am therefore also copying the file called "date file" so you can see what the correct format should be.


What I am asking is can you not adjust the vba code is Book2 so that it can correspond with the format and value in date file?


I removed sensitive information.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
For i = 6 To 24
If Cells(i, "B").Value <> "" And Cells(i, "H") <> "" And Cells(i, "J") = "" Then

Cells(i, "J").Value = Date & " " & time
Cells(i, "J").NumberFormat = "m/d/yyyy h:mm"


End If
Next