Convert Google Sheet date serial value into human readable date

user_7326714 0
Viewed 1093

Set-up

I'm reading a Google Sheet in Python through Google Drive API.

I've got a value for a date – a date serial value, which I need to convert into a human readable date.

I have:

date = 42747

I know that this implies that the date is 12-1-2017.

Question

Is there an easy way to convert 42747 to 12-1-2017?

I've seen this question, but it's not answering mine.

google-sheets date-conversion date python
2 Answers

This would work:

import datetime
def convert_xls_datetime(xls_date):
    return (datetime.datetime(1899, 12, 30)
            + datetime.timedelta(days=xls_date))

print(convert_xls_datetime(42747))
user_85471980

This lists separate functions for date and datetime.

import datetime
from typing import Union

_BASE_DATE = datetime.date(1899, 12, 30)
_BASE_DATETIME = datetime.datetime(_BASE_DATE.year, _BASE_DATE.month, _BASE_DATE.day)

def convert_date_from_gsheet(num_days: int, /) -> datetime.date:
    # Ref: https://stackoverflow.com/a/66738817/
    assert isinstance(num_days, int)
    return _BASE_GSHEET_DATE + datetime.timedelta(num_days)


def convert_date_to_gsheet(date: datetime.date, /) -> int:
    # Ref: https://stackoverflow.com/a/66738817/
    assert isinstance(date, datetime.date)
    return (date - _BASE_GSHEET_DATE).days

def convert_datetime_to_gsheet(dt: datetime.datetime, /) -> float:
    # Ref: https://stackoverflow.com/a/66738817/
    assert isinstance(dt, datetime.datetime)
    delta = dt - _BASE_GSHEET_DATETIME
    return delta.days + delta.seconds / 86_400.0  # Note: datetime.timedelta(days=1).total_seconds() == 86_400.0

def convert_datetime_from_gsheet(num_days: Union[float, int], /) -> datetime.datetime:
    # Ref: https://stackoverflow.com/a/66738817/
    assert isinstance(num_days, (float, int))
    return _BASE_GSHEET_DATETIME + datetime.timedelta(num_days)
user_8322300
Related