Skip to content

Instantly share code, notes, and snippets.

@alaudet
Last active August 9, 2022 08:43
Show Gist options
  • Save alaudet/307ac574973028497d083f6d43575214 to your computer and use it in GitHub Desktop.
Save alaudet/307ac574973028497d083f6d43575214 to your computer and use it in GitHub Desktop.
Converting Excel dates in 5 number format for python
import datetime
# When dumping to csv excel puts date in 5 digit float format (e.g. 39856.0)
# function converts series of dates to date string.
def date_to_string(digit_date):
new_form = []
for d in digit_date:
try:
x = datetime.date(1899,12,30) + datetime.timedelta(days=float(d))
new_form.append(x.strftime('%Y-%m-%d'))
except:
new_form.append('n/a')
return new_form
# convert dates in a pandas dataframe column to strings
df['column_name'] = date_to_string(df['column_name'])
# convert to pandas datetime format
# n/a values become NaT
df['column_name'] = pd.to_datetime(df['column_name'], errors='coerce')
@carminepat
Copy link

I have this date. First part, before ":" is 5 digits format. Can you help me to parse last part? Is hour, minute, seconds but I don't know how to parse. Example: CREATION_DATE=42433:6382323727

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment