Excel VBA Date to Number: Convert Dates to Serials

In Excel VBA, convert a Date value to a number with CDbl(dateValue). The result is a serial value: its whole-number part represents the date and its decimal part represents the time, though the workbook’s 1900 or 1904 date system matters when you write that number into a cell.

What a date number means in Excel

Excel stores dates as numbers so it can sort them, subtract one date from another, and add days to a date. In the usual 1900 date system, a date in 2024 has a serial in the 45,000 range. A time is stored as a fraction of a day: midnight is .0, noon is .5, and 6 a.m. is .25. Thus a date and time together are represented by one number with an integer and fractional part.

A VBA variable declared as Date is not stored as a formatted text string. VBA uses an OLE Automation date representation, which can be converted to a numeric value with CDbl. For modern dates in a workbook using the 1900 system, that number aligns with the Excel serial you normally see in a cell. The distinction becomes important when working with the 1904 system or dates near the beginning of 1900.

Converting a date to a number does not make it a different calendar date; it exposes the value Excel uses for date arithmetic. Formatting controls whether the cell displays a date such as 15-Jun-2024 or a number such as 45458.5.

Convert a VBA Date with CDbl

Use CDbl when you want the numeric value and want to preserve any time fraction. In this example, the date is June 15, 2024 at noon. In the 1900 date system, the result is 45458.5: 45458 for the date and .5 for noon.

Sub ShowDateSerial()
    Dim dateValue As Date
    Dim serialValue As Double

    ' Build a date at noon on June 15, 2024.
    dateValue = DateSerial(2024, 6, 15) + TimeSerial(12, 0, 0)
    serialValue = CDbl(dateValue)

    ' Display the result in the Immediate window.
    Debug.Print serialValue
End Sub

To run a macro, open the Visual Basic Editor with Alt+F11 on Windows, insert a standard module, and run the procedure. On a Mac, the usual shortcut is Option+F11; depending on the keyboard, you may also need Fn. The Immediate window in the editor displays the result after you run the procedure. Save a copy of the workbook before running any macro: macro changes cannot be undone with Ctrl+Z.

For a date without a time, use DateSerial or DateValue before conversion. For example, CDbl(DateSerial(2024, 6, 15)) returns 45458 in the 1900 system. DateSerial is useful when supplying year, month, and day separately because it creates a date value rather than text.

Why the 1900 and 1904 systems matter

Excel workbooks can use either the 1900 or 1904 date system. The 1900 system is the normal default for current Windows workbooks. The 1904 system was historically used by Excel for Mac, and it may still appear in older files or workbooks shared across platforms. For the same modern calendar date, the serials differ by 1,462 days. A raw number copied between workbooks with different systems can therefore display as a date more than four years away from the intended one.

On Windows, the setting is under File > Options > Advanced, in the section When calculating this workbook, where you can find Use 1904 date system. Check the setting before treating a value returned by CDbl as a worksheet serial or passing a worksheet serial back as a raw number. When you assign a VBA Date value directly to a cell using its Value property, Excel can interpret it as a date; assigning a Double through Value2 is assigning a number, so you must account for the workbook’s date system yourself.

For ordinary work with modern dates, CDbl is generally the direct conversion readers need. If a workbook uses the 1904 system and you are deliberately writing an OLE Automation number as an Excel serial, subtract 1462 for the cell value. Conversely, add 1462 to a 1904-system cell serial before interpreting it as an OLE Automation date. Avoid applying this adjustment blindly: it is for converting between the two date systems, not a routine part of every date conversion.

Write the serial into a worksheet cell

To place a numeric serial in a cell, assign the converted value and set the number format to a number format. The following example uses the first worksheet and adjusts for a workbook set to the 1904 date system. It assumes the workbook contains a worksheet and that you want the resulting value displayed with two decimal places.

Sub WriteDateSerial()
    Dim dateValue As Date
    Dim serialValue As Double
    Dim target As Range

    ' Create a modern date and time.
    dateValue = DateSerial(2024, 6, 15) + TimeSerial(12, 0, 0)
    serialValue = CDbl(dateValue)

    ' Convert to the workbook serial if it uses the 1904 system.
    If ThisWorkbook.Date1904 Then
        serialValue = serialValue - 1462
    End If

    Set target = ThisWorkbook.Worksheets(1).Range("A1")
    target.Value2 = serialValue
    target.NumberFormat = "0.00"
End Sub

With the 1900 system, A1 displays 45458.50. With the 1904 system, the same date displays as 43996.50. Both numbers represent June 15, 2024 at noon in their respective systems. If you prefer the cell to show a calendar date rather than a serial, use a date format such as yyyy-mm-dd hh:mm instead of 0.00. The stored value remains numeric either way.

Keep or remove the time fraction

Choose the conversion based on whether the time is meaningful. CDbl preserves it; for example, a date at 6 a.m. ends in .25. That is helpful for elapsed-time calculations, appointments, and timestamp comparisons. If you compare dates that should match by calendar day regardless of time, an unnoticed fraction can cause a mismatch: noon on a day is not numerically equal to midnight on that day.

To discard the time, convert a date-only value. With a value named dateValue, CDbl(DateValue(dateValue)) removes the time portion for ordinary modern dates. For rounding to a whole day, be deliberate: VBA’s Int rounds down toward negative infinity, while Fix truncates toward zero. For positive modern Excel serials, both will remove the fractional part, but they differ for negative numbers. If the requirement is specifically to keep the calendar date, using DateValue states that intention more clearly.

Pick the right conversion for the job

Different-looking conversions answer different questions. CDbl(dateValue) returns a numeric representation and retains a time fraction. CLng(dateValue) converts to a Long integer and rounds rather than simply discarding the fraction, so it is usually not the right choice when you need an exact date serial. A rounded noon value can become the next integer, which may look like a one-day shift.

If you need the number of days between two dates, subtract the dates or use a date-difference method rather than converting both to text. If you need only the calendar date, remove the time first. If you need a readable label, format the value as text only at the point of display; text is less convenient for later date arithmetic. In short:

Use CDbl to expose the serial and preserve time.
Use DateValue before conversion when the time should be discarded.
Use a cell number format to change appearance without changing the stored date.
Check the workbook date system before writing raw serials between workbooks.

Common conversion problems

A cell showing a large number instead of a date is not necessarily broken. It may simply have General or Number formatting. Select the cell and apply a date format if you want a calendar display, or use a number format to inspect the serial. In Excel for Microsoft 365 on Windows, the number-format choices are on Home > Number. Formatting changes how the value looks, not the underlying number.

A date that shifts by exactly 1,462 days usually points to a 1900-versus-1904 system mismatch. A value off by a fraction of a day often means the source includes a time. A date that appears as text may not convert as expected because regional date conventions can interpret strings such as 03/04/2024 differently. Prefer a typed VBA Date, DateSerial, or a cell that contains a real Excel date instead of relying on ambiguous text.

There is also a historical Excel quirk: the 1900 date system includes the fictitious date February 29, 1900, retained for compatibility with older spreadsheet behavior. VBA and OLE Automation do not treat that nonexistent calendar date as a real date in the same way. Consequently, conversions around January and February 1900 need special handling; do not assume the simple modern-date relationship works for every early serial. Most business dates are far later, so this edge case does not affect typical use.

Frequently Asked Questions

What does CDbl do to a VBA Date?
CDbl converts the Date value to a Double number. Its fractional part represents time, so noon contributes .5. For modern dates in a 1900-system workbook, this corresponds to the familiar Excel serial.

How do I convert a date to a number without the time?
Convert the date-only portion with CDbl(DateValue(dateValue)). That removes the time component before returning the numeric value.

Why is my date serial off by 1,462?
The source and destination may use different date systems. Excel’s 1900 and 1904 systems assign different serials to the same modern date, with a 1,462-day difference.

Should I use CLng or CDbl for an Excel date?
Use CDbl when you need the serial, especially if time matters. CLng rounds to an integer and can move a time value to a neighboring day.

Does changing the number format convert the date?
No. A number format changes the display, not the stored value. A serial can be displayed as a date, and a date can be displayed as a serial, while the underlying numeric value remains the same.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.