无法在VBA，Excel 2012中正确格式化日期 [英] Can't format the date properly in VBA, Excel 2012

问题描述

我正在Excel 2010中的VBA脚本中，根据事件的日期从MySQL数据库中选取某些事件。数据库中的日期存储在yyyy-mm-dd hh：mm：ss中。我的用户将使用最新的范围来过滤他们感兴趣的事件。为了使他们更容易，我使用两个日历控件（mscal.ocx）作为范围 - 从Calendar1到Calendar2。我写了两个简单的子例程，以确保日历中的每次点击都会自动更新单元格，从中获取查询字符串的数据，如下所示：

  Private Sub Calendar1_Click（）
 
 Dim date1，datetime1，time1 As Date 
 
单元格（29，10）= CDbl（Calendar1.Value）
 Cells（29，10）.NumberFormat =yyyy-mm-dd
单元格（30，10）.NumberFormat =hh：mm：ss
单元格（31，11）.NumberFormat =yyyy-mm-dd hh：mm：ss
 
 date1 = Cells（29，10）
 time1 =单元格（30，10）
 datetime1 = date1& & time1 
单元格（31，10）= datetime1 
 
 
单元格（29，10）。选择
 
 End Sub 
 
 Private Sub Calendar2_Click（）
 
 Dim date2，datetime2，time2 As Date 
 
细胞（29，11）= CDbl（Calendar2.Value）
细胞（29 ，11）.NumberFormat =yyyy-mm-dd
单元格（30，11）.NumberFormat =hh：mm：ss
单元格（31，11）.NumberFormat =yyyy-mm -dd hh：mm：ss
 
 date2 =单元格（29，11）
 time2 =单元格（30，11）
 datetime2 = date2& & time2 
单元格（31，11）= datetime2 
 
 
单元格（29，11）。选择
 End Sub 
 而且，它几乎完成了我想要的操作，这是改变日历控件给出的格式，即dd / mm / yyyy到yyyy-mm-dd。是的，几乎是因为只有当日期值在1到12之间时才可以工作，那么我的日期时间单元格会正确显示为yyyy-mm-dd hh：mm：ss。对于13到31天的值，我的datetime单元格不会被格式化，而是显示为dd / mm / yyyy hh：mm：ss。
 
 
 
现在，值得注意的是，细胞29,10和29,11始终都具有适当的格式（yyyy-mm-dd），无论日期如何，问题出在细胞31,11和31,10。
 
 
 
现在，当我双击单元格时，将光标闪烁，然后按回车键，格式化将执行，格式更改为正确的格式（即使在13到31之间的日期值）。然而，这样做的目的是尽可能地自动化所有内容，所以这不是一个真正的解决方案。如果需要，我可以附上文件，因为我知道这听起来有点可笑，它适用于某些价值观，而不适合别人。
 
 
 
请帮助！
 
 
 
 EDIT :: 
 
 
 
好的，再次感谢您的快速回复，我检查了您的解决方案，我很回头去乞讨使用
  Private Sub CommandButton1_Click（）
 
 Dim date1，date2，datetime1，datetime2，time1，time2 ，time3 As Date 
 
 date1 = Cells（29，10）
 time1 = Cells（30，10）
 datetime1 =格式（date1，dd / mm / yyyy） & &格式（time1，hh：mm：ss）
单元格（31，10）= datetime1 
 
 End Sub 
  
工作非常好，但只有当日期值从1到12时，例如，对于单元格值
 
 
 
日期
 
 
 2012-09-12 
 
 
时间
 
 
 15:00:00 
 
 
答案是我想要的，这是
 
 
 
日期时间
 
 
 2012-09-12 15:00:00 
 
 
但是，一旦我把日期值放在12以上，这是13到31它停止正常工作（是的，我知道这是多么可笑），而我得到的结果是：
 
 
 
日期时间
 
 
 13/09/2012 15:00:00 
 
 
任何建议非常感谢... 
解决方案
使用 Format（） function 
 格式（17/07/2012，yyyy-mm-dd）
  
结果是 2012-07-17  
 
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 :
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".

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.

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.

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

Date 

  
2012-09-12
Time

  
15:00:00
The answer is as I want it, which is 

Datetime

  
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 :

Datetime

  
13/09/2012 15:00:00
Any suggestions greatly appreciated...
解决方案
use the Format() function
Format("17/07/2012", "yyyy-mm-dd")
the result is 2012-07-17

                        
这篇关于无法在VBA，Excel 2012中正确格式化日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！