[SOLVED] How can I get today's date in a cell in dd/mm/yyyy format?

How can I get today's date in a cell in dd/mm/yyyy format?

I want to put today's date (4 November 2018) into a cell in this (dd/mm/yyyy) format:

    04/11/2018

However every attempt so far leads to (mm/dd/yyyy format):

    11/04/2018

Original code is:

    Dim sToday As String
    sToday = Date
    Cells(nCurrentRow, nCurrentColumn) = sToday

What I have tried includes:

    Cells(nCurrentRow, nCurrentColumn).Value = sToday

and:

    Cells(nCurrentRow, nCurrentColumn).Select
    Selection.NumberFormat = "dd/mm/yyyy;@"

and:

    sToday = Format(Date, "dd-mm-yyyy")

When I output the string in a message box, it always appears in the desired format: 04/11/2018

Even when I post the date as a plain string in the correct format 04/11/2018 the format still changes!

Windows 10 regional settings are as I require (UK date format).

The cell format is also set to dd/mm/yyyy.

Solution

I think your algorithm is getting bollixed by VBA US-Centric date preferences. No matter your windows short date regional setting, all you need do is insert the variable as a Date (not as a string).

When VBA converts today's date to a string, it is in the US Format. Therefore numberformat in the destination cell will not have any affect on a string, nor will the VBA format function. Both of those act on numbers/dates but not on strings, the way you are using it.

For example, try:

With Cells(1, 1)
    .NumberFormat = "dd/mm/yyyy"
    .Value = Date
End With

or, using your variable assignments:

   Dim dToday As Date
dToday = Date

With Cells(nCurrentRow, nCurrentColumn)
    .Value = dToday
    .NumberFormat = "dd/mm/yyyy"
End With