无法在 VBA、Excel 2012 中正确格式化日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11295495/
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
Can't format the date properly in VBA, Excel 2012
提问by Errtu
I'm working on a VBA script in Excel 2010 that picks up certain events from a MySQL database based on the dates of the events. The dates in the database are stored in a "yyyy-mm-dd hh:mm:ss" . My users will be using a from-to date range to filter the events they are interested in. To make it easier for them I use two calendar controls (mscal.ocx) for the range - Calendar1 for from and Calendar2 for to. I wrote two simple subroutines to make sure that every click on the calendars automatically updates cells from which i take data for the query string, which looks like this :
我正在 Excel 2010 中处理一个 VBA 脚本，该脚本根据事件的日期从 MySQL 数据库中选取某些事件。数据库中的日期存储在 "yyyy-mm-dd hh:mm:ss" 中。我的用户将使用起始日期范围来过滤他们感兴趣的事件。为了方便他们，我使用两个日历控件 (mscal.ocx) 作为范围 - Calendar1 用于 from 和 Calendar2 用于 to。我编写了两个简单的子程序，以确保每次点击日历都会自动更新我从中获取查询字符串数据的单元格，如下所示：
Private Sub Calendar1_Click()
Dim date1, datetime1, time1 As Date
Cells(29, 10) = CDbl(Calendar1.Value)
Cells(29, 10).NumberFormat = "yyyy-mm-dd"
Cells(30, 10).NumberFormat = "hh:mm:ss"
Cells(31, 11).NumberFormat = "yyyy-mm-dd hh:mm:ss"
date1 = Cells(29, 10)
time1 = Cells(30, 10)
datetime1 = date1 & " " & time1
Cells(31, 10) = datetime1
Cells(29, 10).Select
End Sub
Private Sub Calendar2_Click()
Dim date2, datetime2, time2 As Date
Cells(29, 11) = CDbl(Calendar2.Value)
Cells(29, 11).NumberFormat = "yyyy-mm-dd"
Cells(30, 11).NumberFormat = "hh:mm:ss"
Cells(31, 11).NumberFormat = "yyyy-mm-dd hh:mm:ss"
date2 = Cells(29, 11)
time2 = Cells(30, 11)
datetime2 = date2 & " " & time2
Cells(31, 11) = datetime2
Cells(29, 11).Select
End Sub
And well, it almost does what I want it to do, which is change the format that is given by the calendar control, which is dd/mm/yyyy to yyyy-mm-dd. Yes, almost, because it only works as long as the day value is between 1 and 12, then my datetime cell is displayed properly as "yyyy-mm-dd hh:mm:ss". For 13 to 31 day value my datetime cell does not get formatted and instead displays as "dd/mm/yyyy hh:mm:ss".
好吧，它几乎完成了我想要它做的事情，即更改日历控件给出的格式，即 dd/mm/yyyy 到 yyyy-mm-dd。是的，几乎，因为它只在日期值介于 1 和 12 之间时才有效，然后我的日期时间单元格正确显示为“yyyy-mm-dd hh:mm:ss”。对于 13 到 31 天的值，我的日期时间单元格没有格式化，而是显示为“dd/mm/yyyy hh:mm:ss”。
Now, it's worth noting, that Cells 29,10 and 29,11 ALWAYS have the proper formatting (yyyy-mm-dd) regardless of the date, the problem is with Cells 31,11 and 31,10.
现在，值得注意的是，无论日期如何，单元格 29,10 和 29,11 始终具有正确的格式 (yyyy-mm-dd)，问题在于单元格 31,11 和 31,10。
Now, when i doubleclick the cell, get cursor flashing in it and press enter, the formatting is executed and the format changes to proper format (even for day value between 13 and 31). However the purpose of this is to automate everything as much as possible, so that's not really a solution. I can attach the file if needed, because I do realise it sounds a bit ridiculous that it works for certain values and not for others.
现在，当我双击单元格，让光标在其中闪烁并按 Enter 键时，将执行格式化并将格式更改为正确的格式（即使是 13 到 31 之间的日期值）。然而，这样做的目的是尽可能地自动化一切，所以这不是一个真正的解决方案。如果需要，我可以附加该文件，因为我确实意识到它适用于某些值而不适用于其他值听起来有点荒谬。
Please help !
请帮忙 ！
EDIT ::
编辑 ：：
Ok, thanks again for fast answer, I checked your solution and with it I'm kind of back to the beggining. Using
好的，再次感谢您的快速回答，我检查了您的解决方案，有了它，我又回到了起点。使用
Private Sub CommandButton1_Click()
Dim date1, date2, datetime1, datetime2, time1, time2, time3 As Date
date1 = Cells(29, 10)
time1 = Cells(30, 10)
datetime1 = Format(date1, "dd/mm/yyyy") & " " & Format(time1, "hh:mm:ss")
Cells(31, 10) = datetime1
End Sub
Works great, BUT works only if the day value is from 1 to 12, for example, for Cell values
效果很好，但仅当日值为 1 到 12 时才有效，例如，对于单元格值
Date
日期
2012-09-12
2012-09-12
Time
时间
15:00:00
15:00:00
The answer is as I want it, which is
答案是我想要的，就是
Datetime
约会时间
2012-09-12 15:00:00
2012-09-12 15:00:00
BUT as soon as I put the day value anything above 12, which is 13 to 31 it stops working properly (yes I know how ridiculous it sounds) and the result I get instead is :
但是，一旦我将日期值设置为 12 或 13 到 31 以上，它就会停止正常工作（是的，我知道这听起来多么荒谬），而我得到的结果是：
Datetime
约会时间
13/09/2012 15:00:00
13/09/2012 15:00:00
Any suggestions greatly appreciated...
任何建议非常感谢...
回答by Ould Abba
use the Format()function
使用Format()函数
Format("17/07/2012", "yyyy-mm-dd")
the result is 2012-07-17
结果是 2012-07-17
回答by umair.ali
I know I'm too late, But I think you need to differentiate between months (MM) and minutes (mm)...So try this...
我知道我为时已晚，但我认为您需要区分月 (MM) 和分钟 (mm)...所以试试这个...
Format(yourDateObj, "yyyy/MM/dd hh:mm:ss");
Hope this Helps..:)
希望这可以帮助..：）
回答by william-1066
Within VBA don't Dim as Date if aiming to pass a datetime as part of a query to SQL. Instead try Dim as String.
如果旨在将日期时间作为查询的一部分传递给 SQL，则在 VBA 中不要将日期显示为日期。而是尝试 Dim as String。
You should then be able to format the string to the desired format using function suggested here already.
然后，您应该能够使用此处建议的函数将字符串格式化为所需的格式。
so for example:
所以例如：
Dim date1 As String
date1 = Cells(29, 10)
date1 = Format(Date1, "yyyy-MM-dd hh:mm:ss")
debug.print date1
I assume this is no longer of use to you (posted over a year ago!) but I have just come across the same problem, and this worked for me, maybe it will help someone else.
我认为这对您不再有用（一年前发布！）但我刚刚遇到了同样的问题，这对我有用，也许它会帮助其他人。
回答by Trace
Private Sub testje()
Dim date1, date2, datetime1, datetime2, time1, time2 As Date
date1 = Cells(1, 1)
time1 = Cells(1, 2)
datetime1 = Format(date1, "dd/mm/yyyy") & " " & Format(time1, "hh:mm:ss")
Cells(2, 1) = datetime1
End Sub
Seems to work. .. Otherwise, set the Range format in Excel to Custom with definition "dd/mm/yyyy uu:mm:ss".
似乎工作。.. 否则，将 Excel 中的范围格式设置为自定义，定义为“dd/mm/yyyy uu:mm:ss”。
回答by Trace
Try format ("mm/dd/yyyy") instead.
尝试使用格式 ("mm/dd/yyyy") 代替。
