Python 3: Convert decimals to date time

Question

I have a Json with contents:

{
    "Name": "startTime",
    "Value": [
        {
            "Valid": true,
            "Time": 43852.491953472221
        }
    ]
}

where the "Time" in decimals 43852.491953472221 tells in my knowledge the number of days since 1st January 1900.

Is there fast way to convert this time in Python into such format as:

import time
print("Start time: ", time.ctime(time.time()))

Output:

Start time:  Wed Jan 22 14:10:50 2020

I strongly suspect that you have an *EXCEL datetime ordinal*, in which case this is a duplicate of [How to convert a given ordinal number (from Excel) to a date](//stackoverflow.com/q/29387137) — Martijn Pieters, Jan 22 '20 at 12:32

Martijn Pieters · Accepted Answer · 2020-01-22T13:11:32.557

Use a datetime.timedelta() object, as well as a datetime.datetime() value for the epoch:

from datetime import datetime, timedelta

EPOCH = datetime(1900, 1, 1)  # midnight 1st January 1900

def from_ordinal(ordinal, _epoch=EPOCH):
    return _epoch + timedelta(days=ordinal)

Demo:

>>> from_ordinal(43852.491953472221)
datetime.datetime(2020, 1, 24, 11, 48, 24, 780000)

Note that the specific example you gave is 2 days into the future (47 hours and 20 minutes, or thereabouts).

If you meant this to be today, with a margin of time for creating your post, then your epoch interpretation isn't quite correct. You probably have an Excel decimal timestamp value; the epoch is really 1899-12-31 00:00:00, with a leap-year bug-as-feature inherited from Lotus 1-2-3, in which case you need to subtract 1 for any value equal to or higher than 60:

EXCEL_EPOCH0 = datetime(1899, 12, 31)

def from_excel_ordinal(ordinal, _epoch=EXCEL_EPOCH0):
    if ordinal >= 60:
        ordinal -= 1  # Excel / Lotus 1-2-3 leap year bug, 1900 is not a leap year!
    # Excel doesn't support microseconds, ignore them as mere fp artefacts
    return (_epoch + timedelta(days=ordinal)).replace(microsecond=0)

This produces:

>>> from_excel_ordinal(43852.491953472221)
datetime.datetime(2020, 1, 22, 11, 48, 24)

Thank you, this was exactly my problem! – eemilk Jan 22 '20 at 12:43 — eemilk, Jan 22 '20 at 12:43

Python 3: Convert decimals to date time

1 Answers1