VBA 使用 format() 或 CDate() 将字符串转换为日期时出现问题

发布于 2024-12-05 14:18:49 字数 1092 浏览 0 评论 0原文

如果以前曾问过这个问题，请为我指出正确的方向。我似乎无法用我的谷歌搜索技能找到任何有用的东西。

我有以下代码，它读取这样的字符串；停电开始日期： 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

这是我通常得到的输出：

sStartTime after reading:  05/10/11 23:59
sStartTime after formatting: 10/05/11 23:59

但有时它甚至会交换日期和年份：

sStartTime after reading:  14/07/11 23:59
sStartTime after formatting: 11/07/14 23:59

然后 CDate 完全塞满东西，将日期转换为 1931 之类的东西。任何将日期字符串转换为日期对象的帮助将不胜感激。

===============

编辑：可能应该在最初的帖子中提到这一点。读取字符串背后的想法是转换为 Date 对象，以便我可以创建日历约会。目前我用这个；

dStartTime = CDate(sStartTime)

我认为这是问题所在， sStartTime = "29/09/11 23:00" (dd/mm/yy hh:mm) 和 dStartTime = "11/9/2029 11:00:00 PM"

所以显然有一些那里发生转换问题，但我不知道我应该将什么格式提供给 CDate 函数才能转换 29/09/11 23:00 进入等效日期对象。

原文

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.

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

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.

===============

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;

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"

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

何以笙箫默 2024-12-12 14:18:49

Format(sStartTime, "dd/mm/yy hh:mm") 无法正常工作，因为 sStartTime 是字符串，而不是日期。
您必须做一些额外的工作才能获得正确输入的日期，例如
dStartTime= DateSerial(Mid(sStartTime,10,2),Mid(sStartTime,7,2),Mid(sStartTime,4,2)) + TimeSerial(...)
等等...
然后，您将能够正确应用您的 Format 函数。