CODEWITHSUNDEEP
excelvbadateexcel-2016regional-settings
Reinstate Monica - Goodbye SE
Reinstate Monica - Goodbye SE

Reputation: 3572

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.

Upvotes: 2

Views: 19212

Answers (4)

user24920387
user24920387

Reputation: 1

Session.findById("wnd[0]/usr/ctxtSP$00007-HIGH").Text = Format(Now - 1, ("dd.mm.yyyy"))    'Yesterday

Upvotes: 0

pnuts
pnuts

Reputation: 59460

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

Since your configuration is UK format:

Ctrl+;

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

Upvotes: 3

Igelaty
Igelaty

Reputation: 89

Sub test()
Dim sToday As String
sToday = Date
sToday = Format(sToday, "dd mm yyyy")

MsgBox sToday
Cells(1, 1).Value = sToday
End Sub

I tried this, and I got the wished result. The workaround is to save the date in a string and format the string.

Upvotes: 3

Related Questions