Forum Discussion

Doris1785's avatar
Doris1785
Copper Contributor
Jul 06, 2022
Solved

VBA date formatting regardless of regional setting

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

    • Doris1785's avatar
      Doris1785
      Copper Contributor

      HansVogelaar,

       

      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.

       

       

Resources