将字符串变量转换为日期 [英] Convert string variable to date

问题描述

我需要将字符串变量转换为VBA宏中的日期变量，以处理日期以获取年份和年份，以便命名工作表

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

Call GetMonthandYear(MonthYear, FileDate)
    ActiveSheet.Name = MonthYear

我希望创建一个名为GetMonthandYear的方法。 FileDate是日期格式的字符串， dd.MM.yyyy HH-mm-ss 。如果我可以将变量更改为日期，我可以将格式更改为 MMMM / yyyy ，然后使用 ToString 我想，并将其分配给 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/yyyy and then use ToString, I think, and assign it to MonthYear.

有没有办法将字符串更改为日期？ / p>

Is there a way to change the string to a date?

推荐答案

您提出的aaproach有几个问题：

There are several problems with your proposed aaproach:

1. 将字符串转换为日期序列可能是有问题的，如果Excel将日期解释为 dd.MM 或 MM.dd 。由于您提前知道格式，请直接提取月份和年份。


2. \ 不是表单名称的有效字符。我已经使用 _ ，替换为你想要的

1. Converting strings to date serial can be problematic, with uncertanty if Excel will interpret the date as dd.MM or MM.dd. Since you know the format in advance, extract the month and year directly.
2. \ is not a valid character for sheet names. I've used _, substitute as you wish

---

---

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

像这样调用

Sub Test()
    Dim FileDate As String
    FileDate = "15.04.2012 16-31-18"

    ActiveSheet.Name = GetMonthandYear(FileDate)

End Sub

这篇关于将字符串变量转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！