Member Avatar for floatingshed

I've been given a pile of reports generated by someone who is no longer with us. They each begin with a decimal date and time like this: "41433.662413","41401,250002" I have tried many different methods using datetime and can't figure out how to recover the date. I know (because of a hand written note) that the above refers to 27th May 2013 6am. Any help would be greatly appreciated. Thanks.

  • 3 Contributors
  • 2 Replies
  • 529 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by woooee

Dani AI

Generated

Short summary tied to the thread: the numeric strings here behave like Excel-style serial dates (whole days = date, fractional part = time) but you need the correct epoch and to handle locale decimals. was on the right track; ’s result is explainable because adding the value to Python’s datetime(1900,1,1) doesn’t use Excel’s conventional origin/quirk and so gives a systematic offset. (howtogeek.com)

A reliable, minimal approach in plain Python:

  • Normalize the string (European comma → dot), parse to float.
  • For most Windows/CSV exports use the Excel 1900 system origin of 1899-12-30 (that origin compensates for Excel’s historical 1900 leap-year quirk). For older Mac workbooks use the 1904 origin. Add the float as days (timedelta) and you get a naive datetime; the fractional part becomes the time of day. If you prefer pandas, pd.to_datetime(..., unit='D', origin='1899-12-30') does the same. (stackoverflow.com)

Example helper (drop this into a small script — it’s different from the snippet already posted in the thread):

from datetime import datetime, timedelta

def excel_serial_to_datetime(s, mac1904=False):
    s = s.strip().replace(',', '.')        # handle "41401,250002"
    val = float(s)
    origin = datetime(1904, 1, 1) if mac1904 else datetime(1899, 12, 30)
    return origin + timedelta(days=val)

Practical troubleshooting notes (why you might still see a date off by N days):

  • If converted dates are consistently off by ~1,462 days the file used the 1904 system; flip mac1904=True. (exceluser.com)
  • If you see a small constant offset (e.g. 12–14 days), check whether the exporter added/subtracted days or whether the CSV values were shifted/truncated; confirm by converting several known sample rows and computing the day-difference to the ground-truth handwritten note.
  • Remember Excel values are timezone-naive; if your reports were produced in a different local timezone, you may need a timezone shift afterwards.

This approach will let you convert the strings robustly and also diagnose why simple datetime(1900,1,1)+timedelta(...) produced the mismatch seen in the thread. (learn.microsoft.com)

Recommended Answers

All 2 Replies

Member Avatar for TrustyTony

Looks like decimal number of full days since one epoch like Microsoft Excel date.

Member Avatar for woooee

That comes out to June 10th, 14 days off. There would be something like 28 leap years which is too far off to be a possibility. "41401,250002" comes back as May 9th at 6:00 AM (providing January 1, 1900 is correct).

import datetime
x=datetime.datetime(1900, 1, 1, 0, 0, 0) + datetime.timedelta(days=41433.662413)
print x.year, x.month, x.day, x.hour, x.minute, x.second
Edited by woooee
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.