How to Convert a Python datetime.datetime to Excel Serial Date Number?

Excel uses a special format to store dates and times, called serial date numbers. Serial date numbers are a count of days since January 1, 1900, which Excel considers as day 1. Python's datetime module provides powerful tools for working with dates, and we can convert datetime objects to Excel's serial format for interoperability.

Understanding Excel Serial Date Numbers

In Excel, dates are internally represented as serial numbers where each day has a unique numeric value. January 1, 1900 is represented by 1, January 2, 1900 by 2, and so on. This numeric format allows Excel to perform date calculations efficiently.

Converting datetime to Excel Serial Date

To convert a Python datetime object to Excel serial format, we calculate the difference from Excel's base date (December 30, 1899) and add fractional days for time components ?

import datetime as dt

def datetime_to_excel_serial_date(date):
    excel_base_date = dt.datetime(1899, 12, 30)  # Excel's actual base date
    delta = date - excel_base_date
    excel_serial_date = delta.days + delta.seconds / (24 * 60 * 60)
    return excel_serial_date

# Example with date and time
my_datetime = dt.datetime(2023, 5, 1, 12, 30, 0)  # May 1, 2023 12:30 PM
excel_serial = datetime_to_excel_serial_date(my_datetime)
print(f"DateTime: {my_datetime}")
print(f"Excel Serial: {excel_serial}")

# Example with date only
my_date = dt.datetime(2023, 5, 1)
excel_serial_date = datetime_to_excel_serial_date(my_date)
print(f"\nDate only: {my_date.date()}")
print(f"Excel Serial: {excel_serial_date}")

DateTime: 2023-05-01 12:30:00
Excel Serial: 45047.520833333336

Date only: 2023-05-01
Excel Serial: 45047.0

Using Python's Built-in Ordinal Method

Python's datetime provides toordinal() method, but it uses a different epoch (January 1, Year 1) than Excel ?

import datetime as dt

# Using toordinal() - different from Excel format
current_date = dt.datetime.now()
python_ordinal = current_date.toordinal()
print(f"Current date: {current_date.date()}")
print(f"Python ordinal: {python_ordinal}")

# Convert Python ordinal to Excel serial (approximate)
# Excel starts from Jan 1, 1900 (ordinal 693594)
excel_serial_approx = python_ordinal - 693593  # Adjust for Excel base
print(f"Approximate Excel serial: {excel_serial_approx}")

Current date: 2024-01-15
Python ordinal: 738887
Approximate Excel serial: 45294

Handling Excel's Leap Year Bug

Excel incorrectly treats 1900 as a leap year. For accurate conversion, we need to account for this bug ?

import datetime as dt

def accurate_excel_serial_date(date):
    excel_base = dt.datetime(1899, 12, 30)
    delta = date - excel_base
    serial = delta.days + delta.seconds / 86400  # 86400 seconds in a day
    
    # Adjust for Excel's leap year bug (1900 not a leap year but Excel thinks it is)
    if date >= dt.datetime(1900, 3, 1):
        serial += 1
        
    return serial

# Test with dates before and after the bug
dates_to_test = [
    dt.datetime(1900, 2, 28),  # Before the bug
    dt.datetime(1900, 3, 1),   # After the bug  
    dt.datetime(2023, 5, 1)    # Modern date
]

for test_date in dates_to_test:
    serial = accurate_excel_serial_date(test_date)
    print(f"{test_date.date()} → {serial}")

1900-02-28 ? 59.0
1900-03-01 ? 61.0
2023-05-01 ? 45048.0

Comparison of Methods

Conclusion

Converting Python datetime to Excel serial dates requires calculating days from Excel's base date (December 30, 1899). For maximum accuracy, account for Excel's 1900 leap year bug by adding 1 to dates after March 1, 1900.