vba 如何将 20120103 之类的数字转换为 Excel 可以识别的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11089270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I convert a number like 20120103 into a date that Excel can recognize?
提问by phan
I have an 8 digit number that tells me the date, YYYYMMDD. How can I convert this number into a date that Excel will recognize as a date.
我有一个 8 位数字，它告诉我日期，YYYYMMDD。如何将此数字转换为 Excel 将识别为日期的日期。
Let's assume cell A1 has 20120229 in it...what do I do?
让我们假设单元格 A1 中有 20120229 ......我该怎么办？
回答by Jean-Fran?ois Corbett
Since you tagged this question VBA, I assume you want an answer in VBA, so here you go:
既然你标记了这个问题 VBA，我假设你想在 VBA 中得到答案，所以你去：
Dim l As Long
Dim s As String
Dim d As Date
l = Range("A1").Value ' 20120229
' convert it to a string
s = CStr(l)
' can now use string functions to parse it
d = DateSerial(CInt(Left(s, 4)), CInt(Mid(s, 5, 2)), CInt(Right(s, 2)))
' d is now 29 Feb 2012
' write it back to the sheet
Range("A2").Value = d
回答by Máté Gelei
Use this formula: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))where A1 is the cell coordinate.
使用这个公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))其中 A1 是单元格坐标。
回答by Aaron Bray
I like being able to select text in excel and call a macro to do the work.
我喜欢能够在 excel 中选择文本并调用宏来完成这项工作。
Sub YYYYMMDDToDate()
Dim c As Range
For Each c In Selection.Cells
c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
'Following line added only to enforce the format.
c.NumberFormat = "mm/dd/yyyy"
Next
End Sub
