How to convert a given ordinal number (from Excel) to a date

by

The offset in Excel is the number of days since 1900/01/01, with 1 being the first of January 1900, so add the number of days as a timedelta to 1899/12/31:

from datetime import datetime, timedelta

def from_excel_ordinal(ordinal: float, _epoch0=datetime(1899, 12, 31)) -> datetime:
    if ordinal >= 60:
        ordinal -= 1  # Excel leap year bug, 1900 is not a leap year!
    return (_epoch0 + timedelta(days=ordinal)).replace(microsecond=0)

You have to adjust the ordinal by one day for any date after 1900/02/28; Excel has inherited a leap year bug from Lotus 1-2-3 and treats 1900 as a leap year. The code above returns datetime(1900, 2, 28, 0, 0) for both 59 and 60 to correct for this, with fractional values in the range [59.0 – 61.0) all being a time between 00:00:00.0 and 23:59:59.999999 on that day.

The above also supports serials with a fraction to represent time, but since Excel doesn’t support microseconds those are dropped.

More Related Contents:

  1. Export a list of stock prices to excel
  2. How to write to an existing excel file without overwriting data (using pandas)?
  3. Convert Excel style date with pandas
  4. Running an Excel macro via Python?
  5. How to convert integer into date object python?
  6. Get Excel-Style Column Names from Column Number
  7. Python Pandas – Read csv file containing multiple tables
  8. Not able to install Python packages [SSL: TLSV1_ALERT_PROTOCOL_VERSION]
  9. Using Pandas to pd.read_excel() for multiple worksheets of the same workbook
  10. How do I pass extra arguments to a Python decorator?
  11. How do I read a date in Excel format in Python?
  12. Accessing attributes on literals work on all types, but not `int`; why? [duplicate]
  13. Matplotlib DateFormatter for axis label not working
  14. Split string using a newline delimiter with Python [duplicate]
  15. Splitting timestamp column into separate date and time columns
  16. How to parse string dates with 2-digit year?
  17. Python write to a file returns empty file
  18. How to write/update data into cells of existing XLSX workbook using xlsxwriter in python
  19. Python reading from a file and saving to utf-8
  20. How do I check if raw input is integer in python 2.7?
  21. Is there a need to close files that have no reference to them?
  22. Write and read a list from file
  23. Flask broken pipe with requests
  24. Wait until task is completed on Remote Machine through Python [duplicate]
  25. Python json.loads changes the order of the object
  26. How can I understand a .pyc file content
  27. fuzzy timestamp parsing with Python
  28. How to avoid “WindowsError: [Error 5] Access is denied”
  29. How to save python screen output to a text file
  30. numpy.sum() giving strange results on large arrays

Leave a Comment