Problems with reformatting date in VBA

[Yamezz]

I have a macro that uses the following bits of code:

Dim ReportDate As Date, strReportDate As String
ReportDate = Date
strReportDate = Format(Date, "dd mmmm yyyy")

strReportDate evaluates to 14 December 2023 when I hover over it in the VBE
I then use strReportDate to enter into column N

Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = strReportDate

But this results in 14-Dec-23 in the cell. Excel has automatically formatted it to d-mmm-yy. This is not what I need - I need the text of strReportDate (14 December 2023)
What am I doing wrong?

 

[Anthony47]

Try

Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate

 

[Alex Blakenburg]

Why do you want to enter your date as text ? By doing so you limit it usefullness.
You could just format the cell or column to show you the date in format you need without forcing it to be text.

    Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1).Value2 = ReportDate
    Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1).NumberFormat = "dd mmmm yyyy"

 

[Yamezz]

Thanks Anthony!

 

[Yamezz]

Why do you want to enter your date as text ? By doing so you limit it usefullness.
You could just format the cell or column to show you the date in format you need without forcing it to be text.

    Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1).Value2 = ReportDate
    Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1).NumberFormat = "dd mmmm yyyy"

The point of this macro is to prepare a .csv file for import into the Xero accounting software, which will be confused by "45274" when it's looking for "14 December 2023"

 

[Alex Blakenburg]

Fair enough, thanks for clarifying.
If you create the csv file using Save As typically the number formatting you apply to cell will be reflected in the csv file. As such the issue is often the other way around ie that number formatting with commas or dollar signs or brackets has been applied and appear in the csv file.
But there are other ways of creating a csv file in which case the conversion to text may be helpful.