Convert Google Sheet date serial value into human readable date

Question

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.

score 2 · Accepted Answer · answered Nov 27 '17 at 10:07

2

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))

answered Nov 27 '17 at 10:07

mrCarnivore

4,638
2
12
29

Nice! Thanks a lot. It actually gives me `2017-01-12 00:00:00`, is there a way to prevent it from giving a time indication, i.e. just `2017-01-12`? – LucSpan Nov 27 '17 at 10:16
2

`convert_xls_datetime(42747).date()` will give you just the date. – mrCarnivore Nov 27 '17 at 10:31

Asclepius · Answer 2 · 2021-12-01T13:11:10.193

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)

Convert Google Sheet date serial value into human readable date

2 Answers2