将日期从 mm/dd/yyyy 更改为 dd/mm/yyyy VBA

changing date fromat from mm/dd/yyyy to dd/mm/yyyy VBA

提问by scb998

So I know this question has been asked a couple of times over, but I believe my situation is a bit different (happy to be proven wrong of course!)

所以我知道这个问题已经被问过几次了，但我相信我的情况有点不同（当然很高兴被证明是错误的！）

Here is the data flow: a user types a date in a date in a form. They then click a button. My macro then takes that date, runs it through the following function:

这是数据流：用户在表单中的日期中键入日期。然后他们点击一个按钮。我的宏然后获取该日期，通过以下函数运行它：

    Function AddWeekDays(StartDate As Long, Days As Long) As Date
    Dim i As Long
    Dim d As Date

    d = StartDate
    i = 0

    While i < Days
        d = DateSerial(Year(d), Month(d), Day(d) + 1)
        If Weekday(d, vbMonday) < 6 Then
            i = i + 1
        End If
    Wend

    AddWeekDays = d
End Function

Then it formats the date to change it from mm/dd/yyyy to dd/mm/yyyy in the following way:

然后它格式化日期以通过以下方式将其从 mm/dd/yyyy 更改为 dd/mm/yyyy：

Dim deadline As Date
Dim deadline_formatted As Date
Dim DateReceived As String
Dim DateConverted As Date
DateReceived = txt_DateReceived.Text
DateConverted = Format(DateReceived, "dd/mm/yyyy")
deadline = AddWeekDays(DateValue((CStr(DateConverted))), 9)
deadline_formatted = Format(deadline, "dd/mm/yyyy")

However, the deadline_formattedvalue is still coming out in the mm/dd/yyyy format.

但是，该deadline_formatted值仍以 mm/dd/yyyy 格式显示。

As an example, when a user enters 01/05/2017 the program should return deadline_formatted = 12/05/2017, but it returns deadline_formatted = 05/12/2017

例如，当用户输入 01/05/2017 时，程序应该返回deadline_formatted = 12/05/2017，但它返回deadline_formatted = 05/12/2017

I have tried changing the variable type to string to see if that made a difference (it didn't), and have tried directly converting the deadline variable to the required format by using the following code:

我尝试将变量类型更改为字符串以查看是否有所不同（没有），并尝试使用以下代码将截止时间变量直接转换为所需的格式：

deadline = Format(AddWeekDays(DateValue((CStr(DateConverted))), 9),"dd/mm/yyyy")

which still returns the incorrect format.

它仍然返回不正确的格式。

Can anybody out there suggest either:

任何人都可以建议：

• How to fix the formatting issue to get the deadline_formatted into the format dd/mm/yyyy OR
• suggest a "workaround" to flip the "dd" with the "mm" (not ideal obviously, but if it works, it works!)

• 如何修复格式问题以将deadline_formatted 转换为dd/mm/yyyy 格式或
• 建议一个“解决方法”用“mm”翻转“dd”（显然不理想，但如果它有效，它就有效！）

Thanks for any advice!

感谢您的任何建议！

回答by YowE3K

The best way to solve this issue is to actually change your computer's default date/time format to match the method used by the users. (In comments it is stated that the users are Australians but your company is US-based, so the default is probably currently set to be the USA's "mm/dd/yyyy" format.)

解决此问题的最佳方法是实际更改计算机的默认日期/时间格式以匹配用户使用的方法。（在评论中指出用户是澳大利亚人，但您的公司位于美国，因此默认值目前可能设置为美国的“mm/dd/yyyy”格式。）

By ensuring that the computers date/time format is correct, it will allow you to process a date as a Date, and it can be stored in Excel cells as a Date, which then allows any of the Australian users to see it displayed as "dd/mm/yyyy" format while a USA-based colleague would see it displayed as "mm/dd/yyyy".

通过确保计算机日期/时间格式正确，它将允许您将日期作为日期进行处理，并且可以将其作为日期存储在 Excel 单元格中，然后任何澳大利亚用户都可以看到它显示为“ dd/mm/yyyy”格式，而美国同事会看到它显示为“mm/dd/yyyy”。

There is a financial risk to your company caused by forcing users to interact with software using an unfamiliar date system, as accidental entering of dates in the wrong formats can lead to major errors downstream, so it is in the company's best interest to allow you to change the settings to be relevant to the users.

强迫用户使用不熟悉的日期系统与软件进行交互会给您的公司带来财务风险，因为以错误的格式意外输入日期可能会导致下游出现重大错误，因此允许您进行以下操作符合公司的最佳利益将设置更改为与用户相关。

回答by Dy.Lee

the result to be String.

结果是字符串。

Dim deadline As Date
Dim deadline_formatted As String '<~~ change string
Dim DateReceived As String
Dim DateConverted As Date

txt_DateReceived = "01/05/2017"

DateReceived = txt_DateReceived
DateConverted = Format(DateReceived, "dd/mm/yyyy")
'deadline = AddWeekDays(DateValue((CStr(DateConverted))), 9)
deadline = AddWeekDays(CLng(DateConverted), 9) '<~~ change Long
deadline_formatted = Format(deadline, "dd/mm/yyyy")

回答by Variatus

I wouldn't bother about the regional settings. Instead, make sure that all dates are captured as Date() or Now() values (42123 or 42123.5555). On the output side such values can be presented in any format you wish. To ensure that dates are entered correctly my preferred way is to use a date picker. If that can't be done make no rules for entering the date at all, working on the presumption that each user will know how to enter a date on his/her machine. Add a date check, like ISDATE(), which will catch some input errors but not all. You don't need to catch all. You only need to teach users how to input dates on their respective PCs.

我不会理会区域设置。相反，请确保所有日期都被捕获为 Date() 或 Now() 值（42123 或 42123.5555）。在输出端，这些值可以以您希望的任何格式呈现。为了确保正确输入日期，我的首选方法是使用日期选择器。如果不能这样做，则根本不制定输入日期的规则，假设每个用户都知道如何在他/她的机器上输入日期。添加日期检查，如 ISDATE()，它将捕获一些输入错误，但不是全部。你不需要抓住所有。您只需要教用户如何在各自的 PC 上输入日期。

回答by APO69

With this line you don't need anything else.

有了这条线，你不需要其他任何东西。

Range("E:E").TextToColumns FieldInfo:=Array(0, xlDMYFormat)
'1.2.2019 -> 01/02/2019
'2,3,2019 -> 02/03/2019
'3-4-2019 -> 03/04/2019
'4*5*2019 -> 04/05/2019
'5_-6-*2019 -> 05/06/2019
'and so on

Of course you can change the format with

当然，您可以更改格式

xlMDYFormat

回答by Tehscript

It is not directly related to your problem, however I believe it might fix your issues. The manual calculation of adding week days might be the problem here.

它与您的问题没有直接关系，但我相信它可能会解决您的问题。添加工作日的手动计算可能是这里的问题。

There is a built in function to add workdays. You can include/exclude weekends/holidays. The following code replaces your above mentioned code.

有一个内置函数来添加工作日。您可以包括/排除周末/假期。以下代码替换了您上面提到的代码。

Sub AddWeekDays()
    Dim deadline As Date, deadline_formatted As Date
    deadline = txt_DateReceived.Value
    deadline_formatted = Format(Application.WorksheetFunction.WorkDay(deadline, 9), "dd/mm/yyyy")
    'debug.print deadline_formatted
End Sub