Retaining Correct Date Format When Writing Dates to Array

[MartinS]

I have a spreadsheet which generates a couple of sheets of results, and the final sheet can then be pasted into another system. We have an issue with dates being stored in an array that are then switching formats from UK (dd/mm/yyyy) to US (mm/dd/yyyy) when written to the final sheet, and are therefore incorrect, i.e. 09/03/2022 becomes 03/09/2022. My initial workaround was to prefix the date in the array with an apostrophe, which then correctly writes the date as a string to the final tab, but the system that receives the data doesn't like the apostrophe, so am trying to figure out how to ensure the dates written to the (variant) array can retain the correct UK format?
Here is the code that reads the dates and then stores them into the array:

Dim dteStartDate        As Date
Dim dteStdDueDate       As Date
Dim dteRevDueDate       As Date
        '==> Get 'Date received'
        dteStartDate = rngResults.Cells(intRecordRow, 18).Value
        '==> Get 'Standard Due Date'
        dteStdDueDate = rngResults.Cells(intRecordRow, 20).Value
        '==> Get 'Revised Due Date' (only where it is present)
        dteRevDueDate = Empty
        If IsDate(rngResults.Cells(intRecordRow, 21).Value) Then
            dteRevDueDate = rngResults.Cells(intRecordRow, 21).Value
        End If
        '==> Get 'Start date'
        varUploadArray(8, i) = "'" & Format(dteStartDate, "dd/mm/yyyy")
        '==> Get 'Due date'
        varUploadArray(9, i) = "'" & varDueDate(intLoopCount)
        '==> Get 'Client Deliverable Date'
        varUploadArray(10, i) = "'" & GetDueDate(dteStdDueDate, dteRevDueDate)

Where varDueDate is a variant single dimensioned array of dates, and the function GetDueDate returns a date as a string in the format "dd/mm/yyyy"
The two dimensional array contains text, numbers and dates, the array is populated first, then transposed and finally written to the final worksheet. It looks to be that the dates are being adjusted when written to the final array - anyone know how to retain the correct format? I've confirmed the dates are correct when in the array, and after being transposed, so it's writing the array that seems to change the formatting:

    rngOutput.Value = varUploadArray

Thanks
Martin

 

[RoryA]

Why are you using formatted strings prefixed with an apostrophe?

 

[MartinS]

Because they are being switched to the incorrect format when written back to Excel, so this was a workaround. The date retains the correct format with the apostrophe (like in previous versions of Excel where an apostrophe fixed the content as a string)

 

[RoryA]

If you put actual date values into the array, they shouldn't get switched. If you put text values in there (and don't use the apostrophe which makes the output text, not a date) then VBA will interpret them in US format.

 

[MartinS]

If you put actual date values into the array, they shouldn't get switched. If you put text values in there (and don't use the apostrophe which makes the output text, not a date) then VBA will interpret them in US format.

OK that's helpful, thanks. I'm going to start storing the dates as serial numbers instead and then just deal with then by formatting at the end.