title: Can't format the date properly in VBA, Excel 2012
body: <p>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 :</p>
  
  <pre><code>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 &amp; " " &amp; 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 &amp; " " &amp; time2
  Cells(31, 11) = datetime2
  
  
  Cells(29, 11).Select
  End Sub
  </code></pre>
  
  <p>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".</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Please help !</p>
  
  <p>EDIT ::</p>
  
  <p>Ok, thanks again for fast answer, I checked your solution and with it I'm kind of back to the beggining. Using </p>
  
  <pre><code>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") &amp; " " &amp; Format(time1, "hh:mm:ss")
  Cells(31, 10) = datetime1
  
  End Sub
  </code></pre>
  
  <p>Works great, BUT works only if the day value is from 1 to 12, for example, for Cell values</p>
  
  <p>Date </p>
  
  <blockquote>
    <p>2012-09-12</p>
  </blockquote>
  
  <p>Time</p>
  
  <blockquote>
    <p>15:00:00</p>
  </blockquote>
  
  <p>The answer is as I want it, which is </p>
  
  <p>Datetime</p>
  
  <blockquote>
    <p>2012-09-12 15:00:00</p>
  </blockquote>
  
  <p>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 :</p>
  
  <p>Datetime</p>
  
  <blockquote>
    <p>13/09/2012 15:00:00</p>
  </blockquote>
  
  <p>Any suggestions greatly appreciated...</p>