title: Fast Date manipulation in VBA
body: <p>My application works a lot with timestamps. Very often, I need to cut off the <code>DDMMYYYY</code> part of a <code>Date</code> variable. The first guess is </p>
  
  <pre><code>Dim xDate as Date 
  Dim xDDMMYYY as Date : xDDMMYYY = Dateserial(Year(xDate), Month(xDate), Day(xDate))
  </code></pre>
  
  <p>However, this is super slow. So instead, I would like to find a faster way. First, I would try to use the <code>Floor</code> function. So ...</p>
  
  <pre><code>Public Function timeStamp2DDMMYYYY(xStamp As Date) As Date
      timeStamp2DDMMYYYY = WorksheetFunction.Floor(CDbl(xStamp), 1)                                  
  End Function
  </code></pre>
  
  <p>However, this produces a wrong result for e.g. Jan 4th, 1900 where the function returns Jan 3th, 1900. So, I give the second try...</p>
  
  <pre><code>Public Function timeStamp2DDMMYYYY(xStamp As Date) As Date
      ' timeStamp2DDMMYYYY = CDbl(xStamp) - modDblTimeStamps(CDbl(xStamp), 1)              ' Cut the non-whole days off
       timeStamp2DDMMYYYY = WorksheetFunction.Floor(CDbl(xStamp), 1)                             ' Cut the non-whole days off
  End Function
  
  Private Function modDblTimeStamps(xA As Double, xB As Double) As Double
      Dim xRes As Double:   xRes = xA - (CLng(xA / xB) * xB)
      If (xRes &lt; -1 / 86400) Then xRes = xRes + xB    ' A difference between xRes and zero
                                                      ' must be at least one second to prevent
      modDblTimeStamps = xRes
  End Function
  </code></pre>
  
  <p>However, this produces bad behavior for e.g. Aug 31, 1963 23:59:59. </p>
  
  <p>My question is : is there a fast and failsafe way to accomplish this conversion?</p>
  
  <p><strong>EDIT</strong>
  By bad - I should have used the profiler first. For comparison purposes I gathered all the proposed solutions:</p>
  
  <pre><code>Public Function timeStamp2DDMMYYYY_1(xStamp As Date) As Date
      timeStamp2DDMMYYYY_1 = DateSerial(Year(xStamp), Month(xStamp), Day(xStamp))
  End Function
  
  Public Function timeStamp2DDMMYYYY_2(xStamp As Date) As Date
      timeStamp2DDMMYYYY_2 = DateValue(xStamp)
  End Function
  
  Public Function timeStamp2DDMMYYYY_3(xStamp As Date) As Date
      Dim xDDMMYYY As String: xDDMMYYY = DatePart("d", xStamp) &amp; "." &amp; DatePart("m", xStamp) &amp; "." &amp; DatePart("yyyy", xStamp)
      timeStamp2DDMMYYYY_3 = CDate(xDDMMYYY)
  End Function
  
  Public Function timeStamp2DDMMYYYY_4(xStamp As Date) As Date
      timeStamp2DDMMYYYY_4 = CDate(Split(xStamp, Chr(32))(0))
  End Function
  
  Public Function timeStamp2DDMMYYYY_5(xStamp As Date) As Date
      timeStamp2DDMMYYYY_5 = CDbl(xStamp) - modDblTimeStamps(CDbl(xStamp), 1)               
  End Function
  
  Public Function timeStamp2DDMMYYYY_6(xStamp As Date) As Date
     timeStamp2DDMMYYYY_6 = Int(xStamp)
  End Function
  </code></pre>
  
  <p>Going from 01.01.1900 to 31.12.1905 with a 5 minute increment, all functions return the right value. The profiling results are :</p>
  
  <pre><code>timeStamp2DDMMYYYY_1 - 2.22199s
  timeStamp2DDMMYYYY_2 - 5.62594s
  timeStamp2DDMMYYYY_3 - 5.91005s
  timeStamp2DDMMYYYY_4 - 6.51989s
  timeStamp2DDMMYYYY_5 - 3.67998s
  timeStamp2DDMMYYYY_6 - 4.29623s
  </code></pre>
  
  <p>Conclusion - my slow spot is somewhere else in the timeStampManip module. The primitive version is the fastest... Thanks to everybody.</p>