VBA 问题使用 format() 或 CDate() 将字符串转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7481309/
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
VBA Issues converting string to date using format() or CDate()
提问by FizzBuzz
If this has been asked before, please point me in the right direction. I can't seem to find anything useful with my google-ing skills.
如果以前有人问过这个问题，请指出我正确的方向。我似乎找不到任何对我的 google-ing 技能有用的东西。
I have the following code, which reads in a string like this; Outage StartDate: 05/10/11 23:59 EST
And pulls out the date information, i.e. 05/10/11 23:59
我有以下代码，它读入这样的字符串；Outage StartDate: 05/10/11 23:59 EST
并拉出日期信息，即 05/10/11 23:59
sStartTime = Mid(objItem.Body, 18, 15) ' Extract the start time
Debug.Print "sStartTime after reading: " & sStartTime
sStartTime = Format(sStartTime, "dd/mm/yy hh:mm") ' Format date correctly
Debug.Print "sStartTime after formatting: " & sStartTime
This is what output I usually get:
这是我通常得到的输出：
sStartTime after reading: 05/10/11 23:59
sStartTime after formatting: 10/05/11 23:59
But sometimes it swaps the day and year even:
但有时它甚至会交换日期和年份：
sStartTime after reading: 14/07/11 23:59
sStartTime after formatting: 11/07/14 23:59
Then CDate completely stuffs things around, converting dates to such things as 1931...any help converting a date string to a date object would be greatly appreciated.
然后 CDate 完全填充了一些东西，将日期转换为 1931 之类的东西......任何将日期字符串转换为日期对象的帮助将不胜感激。
===============
================
Edit: Should probably have mentioned this in the initial post. The idea behind reading the string, is to convert to a Date object so that I can create a calendar appointment. Current I use this;
编辑：可能应该在最初的帖子中提到这一点。读取字符串背后的想法是转换为 Date 对象，以便我可以创建日历约会。目前我用这个；
dStartTime = CDate(sStartTime)
Which I think is the problem line, sStartTime = "29/09/11 23:00" (dd/mm/yy hh:mm) and dStartTime = "11/9/2029 11:00:00 PM"
我认为这是问题行，sStartTime = "29/09/11 23:00" (dd/mm/yy hh:mm) 和 dStartTime = "11/9/2029 11:00:00 PM"
So obviously there's some conversion issues going on there, but I have no idea what format I'm meant to be feeding to the CDate function in order to turn 29/09/11 23:00 into the equivalent date object.
所以显然那里有一些转换问题，但我不知道我打算向 CDate 函数提供什么格式，以便将 29/09/11 23:00 转换为等效的日期对象。
回答by Patrick Honorez
Format(sStartTime, "dd/mm/yy hh:mm")cannot correctly work since sStartTime is a string, NOT a date.
You have to do some extra work to get a correctly typed Date, likedStartTime= DateSerial(Mid(sStartTime,10,2),Mid(sStartTime,7,2),Mid(sStartTime,4,2)) + TimeSerial(...)etc...
THEN, you will be able to apply your Format function correctly.
Format(sStartTime, "dd/mm/yy hh:mm")无法正常工作，因为 sStartTime 是一个字符串，而不是一个日期。
您必须做一些额外的工作才能获得正确键入的日期，例如dStartTime= DateSerial(Mid(sStartTime,10,2),Mid(sStartTime,7,2),Mid(sStartTime,4,2)) + TimeSerial(...)等...
然后，您将能够正确应用您的格式功能。
