Can't format the date properly in VBA, Excel 2

Question

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...

Answer 1

I know I'm too late, But I think you need to differentiate between months (MM) and minutes (mm)...So try this...

Format(yourDateObj, "yyyy/MM/dd hh:mm:ss");

Hope this Helps..:)

Answer 2

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".

Answer 3

Try format ("mm/dd/yyyy") instead.

Answer 4

use the Format() function

Format("17/07/2012", "yyyy-mm-dd")

the result is 2012-07-17

Answer 5

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.

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.