Menu
When I write a date from VBA into an Excel worksheet, sometimes the value on the worksheet ends up being one less (one day earlier) than I expect. Here's an example that I've tested on a multiple computers with Excel 2003, 2007, and 2010. From the immediate window:
?Format(41652.9999999963, "yyyy-mm-dd hh:mm:ss")
> 2014-01-14 00:00:00
[A1].value = CDate(41652.9999999963)
?Format([A1].value, "yyyy-mm-dd hh:mm:ss")
> 2014-01-13 00:00:00
[A1].value = CDbl(CDate(41652.9999999963))
?Format([A1].value, "yyyy-mm-dd hh:mm:ss")
> 2014-01-14 00:00:00
What exactly triggers this?
EDIT:
Okay, let me be more clear that this isn't a rounding issue. If I go up or down one fraction of a second I get the correct time. It's just that if I hit near this exact number the date goes off by an entire day:
[A1].value = CDate(41652.99999)
?Format([A1].value, "yyyy-mm-dd hh:mm:ss")
> 2014-01-13 23:59:59
[A1].value = CDate(41652.999999999996)
?Format([A1].value, "yyyy-mm-dd hh:mm:ss")
> 2014-01-13 00:00:00
[A1].value = CDate(41652.999999999997)
?Format([A1].value, "yyyy-mm-dd hh:mm:ss")
> 2014-01-14 00:00:00
[A1].value = CDate(41653.00001)
?Format([A1].value, "yyyy-mm-dd hh:mm:ss")
> 2014-01-14 00:00:01
532
Click on Insert tab > select Module. Step 2: Write the subprocedure for VBA Format Date or choose anything to define the module. Step 3: Choose the range cell first as A1. Step 4: Then use the Number Format function as shown below.
However, Excel may sometimes not correctly recognize dates when pasting using the 'Paste Special' option. To solve this issue, force the cells into Text format before you put dates into them: 1. Copy the dates from the Excel spreadsheet as they are (most likely they are in the "Date" format).
Solution: You have to change to the correct value. Right-click on the cell and click Format Cells (or press CTRL+1) and make sure the cell follows the Text format. If the value already contains text, make sure it follows a correct format, for e.g. 22 June 2000.
As stated in Help for CDate, "CDate recognizes date literals and time literals as well as some numbers that fall within the range of acceptable dates. When converting a number to a date, the whole number portion is converted to a date. Any fractional part of the number is converted to a time of day, starting at midnight." It never says that time rounded to next day adds to dates!! I believe that this solves the mystery.
In fact you can use round yourself to get the desired result. Tests demonstrated that 9 digits are the top number for rounding, with 10 providing the undesired effect...
ex [a1]=cdate(round(41652.9999999999,8)) --> 14/01/14 00:00
[a1]=cdate(round(41652.9999999999,10)) --> 13/01/14 00:00
added code after comment:
Sub dower()
Dim v1 As Double, vdt As Date, vv As Variant, vdbl As Double
Range("a1:a6").ClearContents
v1 = 41652.9999999996
vdt = CDate(v1)
vv = CDate(v1)
vdbl = CDate(v1)
[a1] = v1
[a2] = vdt
[a3] = vv
[a4] = vdbl
vdt = CDbl(v1)
[a5] = vdt
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
