将日期转换为数字格式 vba

Convert date to number format vba

提问by user2301233

I'm having issues copying a date from one file to another and preserving the format (dd/mm/yyyy) while using vba. A straight copy results in the dates that can be converted to US date format changing to mm/dd/yyyy and those that cannot (any date with dd > 12) changing to a string. I have tried changing the date to a number first and then copy/paste (with the plan to go back to date format once the copy was complete) but this only changes format for the dates with dd<=12, the dates with dd>12 still come across as string format. Also the dates that successfully change to numbers seem to change to US format fist then to number format so I end up with the wrong date anyway The strange thing is going through these steps manually (outside of vba) works fine, I can get all the dates into whatever format I want and the US format never appears. Here is the code I have been using:

我在使用 vba 时将日期从一个文件复制到另一个文件并保留格式 (dd/mm/yyyy) 时遇到问题。直接复制会导致可以转换为美国日期格式的日期更改为 mm/dd/yyyy，而那些不能（任何 dd > 12 的日期）更改为字符串的日期。我曾尝试先将日期更改为数字，然后复制/粘贴（计划在复制完成后返回日期格式）但这只会更改 dd<=12 的日期格式，dd> 的日期12 仍然以字符串格式出现。此外，成功更改为数字的日期似乎先更改为美国格式，然后更改为数字格式，因此无论如何我最终都会得到错误的日期奇怪的是手动（在 vba 之外）执行这些步骤工作正常，我可以将所有日期转换为我想要的任何格式，而美国格式永远不会出现。这是我一直在使用的代码：

Sheets("Import").Select
PathName = Range("B2").Value
Filename = Range("B3").Value
TabName = Range("B4").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Columns("A:A").Select
Selection.NumberFormat = "0"
Range("A2:A200").Copy Destination:=Workbooks(ControlFile).Sheets("Import").Range("A8:A206")

Any help or suggestions for things to try would be great. Cam

任何尝试的帮助或建议都会很棒。凸轮

回答by Ricky

I would try one of these. I am confused to which format your wanting.

我会尝试其中之一。我对您想要的格式感到困惑。

Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Range("A2:A200").Copy Destination:=Workbooks(ControlFile).Sheets("Import").Range("A8:A206")

'Workbooks(ControlFile).Sheets("Import").Columns("A:A").NumberFormat = "dd/mm/yy;@" 

I am sure one of these will work

我相信其中之一会起作用

'Workbooks(ControlFile).Sheets("Import").Columns("A:A").NumberFormat = "mm/dd/yy;@" 

If all else fails just run this after your code and make it any format your seeking.

如果所有其他方法都失败了，只需在您的代码之后运行它并使其成为您想要的任何格式。

Sub Change_Date()
Dim oCell As Range
Dim LastRow As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For Each oCell In ActiveSheet.Range("A1:A" & LastRow)
    oCell.Value = Format(Trim(oCell.Value), "dd/mm/yyyy")
Next oCell


End Sub

回答by KyloRen

Living in Japan and having to deal with weird date conversions I have learnt the only real safe way to keep the formatting the way you want is to use NumberFormatLocalto set the cell the way you need it. No matter how you format the date in the VBA once it is in the cell, Excel seems to take over from what the local settings are set to.

生活在日本并且不得不处理奇怪的日期转换我已经学会了保持格式设置的唯一真正安全的方法是使用NumberFormatLocal您需要的方式设置单元格。无论您如何在 VBA 中设置单元格中的日期格式，Excel 似乎都会接管本地设置的设置。

The below code is what I would use in your situation to guarantee the format that you want.

下面的代码是我在您的情况下使用的代码来保证您想要的格式。

   Sheets("Import").Select
   PathName = Range("B2").Value
   Filename = Range("B3").Value
   TabName = Range("B4").Value
   ControlFile = ActiveWorkbook.Name
   Workbooks.Open Filename:=PathName & Filename
   ActiveSheet.Name = TabName
   Columns("A:A").Select
   Selection.NumberFormat = "0"
   Range("A2:A200").Copy Destination:=Workbooks(ControlFile).Sheets("Import").Range("A8:A206")

   Workbooks(ControlFile).Sheets("Import").Range("A8:A206").NumberFormatLocal = "d/m/yyyy"

You could also use something like this on a date 1/12/2016

你也可以在约会时使用这样的东西 1/12/2016

Range("A8:A206").NumberFormatLocal = "dd/mmm/yyyy"

To give you an output of,

给你一个输出，

12/Jan/2016instead of Jan/12/2016

12/Jan/2016代替 Jan/12/2016