Excel VBA Convert Date to Number: Serial Values

Yes. Excel dates are stored as serial numbers, so VBA can read a date cell’s underlying number with Range.Value2; to display that number instead of a date, set the destination cell’s number format to General or Number. For a VBA Date variable, writing the date to a worksheet cell and reading back Value2 gives the serial used by that workbook.

What an Excel date number represents

An Excel date is not a special calendar object in a worksheet cell. It is a number that Excel displays using a date format. In the standard 1900 date system, the whole-number portion counts days from Excel’s starting point, and the fractional portion represents the time of day. For example, 0.5 represents noon, while a value with a whole-number portion and a decimal portion represents a date and a time.

In the 1900 date system, January 1, 1900 has serial 1, and January 1, 2024 has serial 45292. Applying a date format to 45292 makes it look like a calendar date; applying General makes the underlying number visible. Changing the format does not change the date itself. Conversely, converting the cell’s value to text produces text, not a numeric serial that Excel can use directly in date arithmetic.

Excel also retains a historical compatibility quirk: its 1900 system treats February 29, 1900 as if it existed. That date did not exist in the Gregorian calendar. It matters mainly for calculations involving very early dates; ordinary modern dates are not affected by the quirk.

Read a worksheet date with Value2

For a date already stored in a worksheet cell, Value2 is usually the most direct VBA approach. It returns the underlying cell value without converting it to VBA’s Date or Currency subtypes. If the source cell contains a genuine Excel date, its value is already numeric; copying that value to another cell and applying General reveals the serial.

The following example assumes the date is in A2 on the first worksheet and puts its serial in B2. It copies the numeric value rather than trying to parse the date’s displayed text.

Sub CopyDateAsSerial()
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim outputCell As Range

    Set ws = ThisWorkbook.Worksheets(1)
    Set sourceCell = ws.Cells(2, 1)
    Set outputCell = ws.Cells(2, 2)

    ' A real worksheet date is stored as a number.
    If IsNumeric(sourceCell.Value2) Then
        outputCell.Value2 = sourceCell.Value2
        outputCell.NumberFormat = "General"
    End If
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so check the source and destination cells first, especially if adapting the code to process a whole column. On Windows, open the VBA editor with Alt+F11 and run a selected macro with Alt+F8. On a Mac, Option+F11 and Option+F8 are common equivalents; some keyboards require Fn as well, and the Developer tab can also open the editor and macro list.

Convert a VBA Date variable

A VBA variable declared as Date is not itself a worksheet cell. You can convert it to a floating-point number with CDbl, but that number follows VBA/OLE Automation date conventions. For modern dates in a standard 1900-system workbook, it agrees with the familiar Excel serial. Very early dates and workbooks using the 1904 system require care, so do not assume a raw CDbl result is the correct worksheet serial in every workbook.

To obtain the serial according to the active workbook’s date system, let Excel store the Date in a cell, then read that cell’s Value2. This example uses A2 as a temporary or intended output cell on the first worksheet. It leaves the numeric serial displayed there.

Sub StoreDateVariableAsSerial()
    Dim ws As Worksheet
    Dim outputCell As Range
    Dim dateValue As Date
    Dim serialValue As Double

    Set ws = ThisWorkbook.Worksheets(1)
    Set outputCell = ws.Cells(2, 1)

    dateValue = DateSerial(2025, 3, 8)
    outputCell.Value = dateValue
    serialValue = CDbl(outputCell.Value2)
    outputCell.Value2 = serialValue
    outputCell.NumberFormat = "General"
End Sub

DateSerial(2025, 3, 8) constructs March 8, 2025 without relying on how a typed date string is interpreted. The final cell contains a number, not a Date-typed display. If the goal is simply to calculate with the VBA variable, rather than to match a particular workbook’s serial system, CDbl(dateValue) can be sufficient for modern dates in a 1900-system workbook.

Convert dates stored as text

A cell that looks like a date may contain text rather than an Excel date value. A value such as an imported 2024-01-15 can be text, depending on how it was imported or entered. In that case, Value2 returns the text characters, not an Excel serial. Changing the cell’s format to General will not convert text into a number.

When text is a date, VBA can parse it with CDate or DateValue, then write the resulting Date to a worksheet cell and read back Value2. However, parsing text such as 03/04/2024 is locale-dependent: it can mean March 4 or April 3. Use a clearly defined input format, validate the components, and avoid relying on ambiguous strings when files move between computers with different regional settings. For a fixed year-month-day format, split the components and construct the date with DateSerial rather than guessing from slashes.

Also check for blank cells, error values, and non-date text before conversion. A robust import macro should report invalid rows or skip them deliberately; it should not silently turn every numeric-looking value into a date. Excel serials can also represent ordinary numbers, so the cell’s appearance alone cannot prove that its contents are a valid date.

Keep or remove the time fraction

Dates with times are numbers with a fractional day. For example, 6:00 AM is one quarter of a day, or 0.25; a date at 6:00 AM therefore has a serial ending in approximately .25. The displayed format may hide this fraction, making a timestamp look like a date-only value even though its time is still present.

Copying Value2 preserves the complete value, including its time fraction. If you want the calendar day only, remove the fractional part deliberately. In VBA, the Int function returns the integer part of a positive Excel date serial; for negative values, note that Int rounds down rather than simply dropping digits. If time matters for later comparisons or calculations, keep the full number and format the cell to show both date and time instead.

Account for the 1900 and 1904 date systems

Excel workbooks can use either the 1900 or 1904 date system. The systems assign different serials to the same modern calendar date: for example, January 1, 2024 is 45292 in the 1900 system and 43830 in the 1904 system, a difference of 1462 days. Excel normally handles a Date written into a cell using that workbook’s setting. This is why reading the value back from the destination cell is safer than assuming a VBA date number will always equal the worksheet serial.

When moving serial values between workbooks, check both workbooks’ date systems. Copying the raw number from a 1900-system workbook into a 1904-system workbook without adjustment can display a date 1462 days away. Copying actual Date values through Excel can allow Excel to interpret them as calendar dates, but code that transfers raw Value2 numbers needs an explicit plan for the system difference. The setting is available under File > Options > Advanced in Excel for Microsoft 365 on Windows; on Mac, look in Excel > Preferences (or Settings, depending on the release) for the workbook’s date-system option.

Choose the right method and avoid common mistakes

Use Value2 when the source is already a worksheet date and you need its stored number. Use a Date variable when your code needs calendar operations, such as constructing a date with DateSerial, and pass that value through a worksheet cell when you need the workbook-specific serial. Parse text only when the source really is text, and make its format rules explicit.

The output still looks like a date: set the output cell’s NumberFormat to General or a numeric format. The value may already be the serial.
The result is a large decimal: the source probably includes a time fraction. Keep it for timestamps or remove it intentionally for date-only work.
The result is text: check whether the source was imported as text. Formatting alone does not parse it.
The date shifts between files: check whether the workbooks use different date systems before copying raw serials.
The parsed date changes by computer: replace ambiguous text parsing with a defined format and explicit date-component handling.

For a one-off conversion, a worksheet’s number format may be enough; a macro is useful when applying a repeatable conversion across many cells or during an import. VBA changes cell contents and formats, so test on a small range and retain the original data until the results are verified.

Frequently Asked Questions

What is the VBA code to convert a date cell to a number?
For a real Excel date in A2, read or copy Range("A2").Value2 and set the destination cell’s NumberFormat to "General". The cell already stores a numeric serial; the format controls how it appears.

Why does VBA return a decimal for my date?
The decimal portion represents time as a fraction of one day. For instance, noon is .5. A date-time value can therefore have a fractional serial even if the cell’s current format hides the time.

Is CDbl(Date) always the same as an Excel serial?
No. VBA’s Date conversion uses OLE Automation conventions, while a workbook can use either Excel date system. For modern dates in the 1900 system the values generally match, but using a worksheet cell and reading Value2 is safer when the workbook’s serial is required.

How do I make the serial show as a number?
Set the cell’s NumberFormat to "General" or an appropriate numeric format. This changes presentation, not the underlying value. If the cell contains text, convert the text to a date value first.

How can I convert a date stored as text?
Parse it using a known, unambiguous format, create a VBA Date, and write that Date to a worksheet cell before reading Value2. Avoid ambiguous inputs such as 03/04/2024 unless the expected month-day order is explicitly known.

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.