Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Milliseconds are rounded to the nearest second when you try to assign the VBA Date format or the Variant Date format to a cell in an Excel worksheet

Milliseconds are rounded to the nearest second when you try to assign the VBA Date format or the Variant Date format to a cell in an Excel worksheet

Symptoms

When you make a programmatic change to the value in a cell in a Microsoft Excel worksheet, the millisecond value that you specify is rounded to the nearest second. This issue occurs when the following conditions are true:
  • The change that you make assigns the VBA Date format or the Variant Date format to the cell.
  • You make the programmatic change by using a Visual Basic for Applications macro or by using an external Automation client.
This issue does not occur when the change that you make assigns the Date format or the Time format to the cell.

↑ Back to the top

Cause

This issue occurs because Excel internally formats date strings according to the date and time format that is specified on the
Regional Options tab of the Regional and Language Options item in Control Panel. This value is rounded to the nearest second. Excel assigns this value to the cell and then recalculates the value to produce a new date value for the cell.

↑ Back to the top

Workaround

Use one of the following methods to work around this issue.

Method 1

You can convert the date value into a string format that contains a fraction of a second. This string value can be assigned to and calculated as a date format that retains the milliseconds.

Method 2

You can use the Value2 property of the Range object to retain the milliseconds. For example, you can use the following VBA code.
Sub CopyValueUsingVBDate()
   Dim d As Date
   d = ActiveSheet.Range("A1").Value
   ActiveSheet.Range("B1").Value2 = d
End Sub
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
182812 Description of the Value2 property for the Range object

↑ Back to the top

Keywords: kb, kbprb, kbtshoot, kbautomation, kbprogramming, kbvba, kbfreshness2006, kboffice12vista, kboffice12yes

↑ Back to the top

Article Info
Article ID : 875526
Revision : 6
Created on : 8/20/2020
Published on : 8/20/2020
Exists online : False
Views : 108