VBA文本到列的读取日期格式为美国 [英] VBA Text-To-Columns Reading Date Format As US

问题描述

在vba中运行文本到列时，只要有可能，它会将我的日期作为美国日期而不是英国日期.因此，例如，8月31日保持为8月31日，但9月1日更改为1月9日！

When running text-to-columns in vba, it puls my dates as american dates, instead of uk dates, as long as it is possible for it to do so. So for example, 31-Aug stays as 31-Aug, but 01-Sep changes to 09-Jan!

在运行vba之前，是否有任何方法可以指定所有日期为英国格式?甚至只是重新格式化为美国日期以显示为美国日期都行不通，因为我们有运行if date < Today的代码，这当然会引起日期格式更改的问题.

Is there any way to specify that all dates are in UK format BEFORE vba runs? As even just reformatting to US dates to show as US dates won't work because we have code that runs if date < Today, which of course will cause issues with the changing date format.

数据以dd/mm/yyyy的格式输入，但是excel将其读取为mm/dd/yyyy！

The data feeds in the format dd/mm/yyyy, but excel reads it as mm/dd/yyyy!

推荐答案

pnuts注释是解决此问题的最简单方法.

pnuts comment is the easiest method to solve this problem.

说明:

运行记录宏"，选择您的列并启动文本到列"向导.

Run the 'Record Macro', select your column and initiate text-to-columns wizard.

在文本到列"向导的第三页上的列数据格式"下，日期"选项自动设置为"MDY".将其更改为"DMY".

On the 3rd page of the text-to-columns wizard, under 'Column data format', the 'Date' option is automatically set to 'MDY'. Change this to 'DMY'.

停止宏，您将找到此代码

Stop the macro and you will find this code

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True

=Array(1,4)中的"4"是确定DMY格式的部分.

The "4" in =Array(1,4) is the part that determines DMY format.

这篇关于VBA文本到列的读取日期格式为美国的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！