SOLVED

VBA date formatting regardless of regional setting

Copper Contributor

Here is an extract of my vba codes:

Dim dtToday As Date

Dim CurrTime As String

Dim Rng As Range

dtToday = Date()  'today date

CurrTime = Time()  'current time

 

Rng.Offset(1).Value = Format(DateValue(dtToday), "m/d/yyyy") & " " & _
Application.WorksheetFunction.Text(CurrTime, "hh:mm:ss")

 

And the output is "6-Jul-2022 10:32:36 AM" as I apply custom formatting to the cell in Excel as follows:

[$-en-US]d-mmm-yyyy h:mm:ss AM/PM

 

Currently it works fine if regional setting in PC is "English (United States)"

If another user with different regional setting runs the vba code, the output becomes "7-Jun-2022 10:32:36 AM".

 

To avoid this issue, how do I code or format date in vba to get the correct date regardless of regional settings?

 

Thank you.

 

2 Replies
best response confirmed by Doris1785 (Copper Contributor)
Solution

@Doris1785 

Try

 

Rng.Offset(1).Value = Now

@Hans Vogelaar,

 

I tested your proposed solution with a few different desktop regional settings. It gave the same result, a consistent date in dd/mm/yyyy format that I'm looking for.

Thank you for this quick and simple solution.

 

 

1 best response

Accepted Solutions
best response confirmed by Doris1785 (Copper Contributor)
Solution

@Doris1785 

Try

 

Rng.Offset(1).Value = Now

View solution in original post