vba 将字符串变量转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10162424/
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
Convert string variable to date
提问by Sam Stephenson
I need to convert a string variable to a date variable in a VBA macro, to process the date to get the mMonth and year in order to name the worksheet
我需要在 VBA 宏中将字符串变量转换为日期变量，以处理日期以获取 mmMonth 和 year 以便命名工作表
Call GetMonthandYear(MonthYear, FileDate)
ActiveSheet.Name = MonthYear
I looking to create a method called GetMonthandYear. FileDate is a String in a date format, dd.MM.yyyy HH-mm-ss. If I can change the variable to a date, I can change the format to MMMM/yyyyand then use ToString, I think, and assign it to MonthYear.
我希望创建一个名为 GetMonthandYear 的方法。FileDate 是日期格式的字符串，dd.MM.yyyy HH-mm-ss. 如果我可以将变量更改为日期，我可以将格式更改为MMMM/yyyy然后使用ToString，我认为，并将其分配给MonthYear.
Is there a way to change the string to a date?
有没有办法将字符串更改为日期？
回答by chris neilsen
There are several problems with your proposed aaproach:
您提出的 aaproach 存在几个问题：
- Converting strings to date serial can be problematic, with uncertanty if Excel will interpret the date as
dd.MMorMM.dd. Since you know the format in advance, extract the month and year directly. \is not a valid character for sheet names. I've used_, substitute as you wish
- 将字符串转换为日期序列可能会出现问题，不确定 Excel 是否将日期解释为
dd.MM或MM.dd。既然事先知道格式，直接提取月份和年份。 \不是工作表名称的有效字符。我用过_，随意替换
Function GetMonthandYear(FileDate As String) As String
Dim dot1 As Long, dot2 As Long
Dim m As String, y As String
dot1 = InStr(FileDate, ".")
dot2 = InStr(dot1 + 1, FileDate, ".")
m = Mid$(FileDate, dot1 + 1, dot2 - dot1 - 1)
y = Mid$(FileDate, dot2 + 1, InStr(FileDate, " ") - dot2 - 1)
GetMonthandYear = Format$(DateSerial(y, m, 1), "MMMM_yyyy")
End Function
Call it like this
像这样称呼
Sub Test()
Dim FileDate As String
FileDate = "15.04.2012 16-31-18"
ActiveSheet.Name = GetMonthandYear(FileDate)
End Sub
回答by Philip Sheard
Have you tried using the CDate and Format functions?
您是否尝试过使用 CDate 和 Format 函数？
回答by Alexander Burov
You can get Date using CDate function but it requires -to be replaced by :and .to be replaced by /.
您可以使用CDate函数获取日期，但它需要-被替换:，并.以被替换/。
Dim yourStringDate, sBuf As String
Dim yourDateVariable As Date
yourStringDate = "15.04.2012 16-31-18"
sBuf = Replace$(yourStringDate,"-", ":")
sBuf = Replace$(sBuf, ".", "/")
yourDateVariable = CDate(sBuf)
MsgBox yourDateVariable
