1

I am searching nearly 2 days, how to convert this type of timestamp format: 2018-07-11T10:40:09+00:00 gathered from excel cell to an epoch?

The code I use is this:

import openpyxl 
import datetime
from datetime import date, datetime, time

path = "/home/UbuntuUser/Desktop/Times.xlsx"
wb_obj = openpyxl.load_workbook(path) 
sheet_obj = wb_obj.active 
cell_obj = sheet_obj.cell(row = 2, column = 3) 
print(cell_obj.value) 
print(datetime.fromisoformat(cell_obj.value))
print(time.mktime(datetime.fromisoformat(cell_obj.value)))

which does not work! (source: https://www.kite.com/python/answers/how-to-convert-the-current-date-to-an-epoch-timestamp-in-python and https://www.geeksforgeeks.org/python-reading-excel-file-using-openpyxl-module/)

I read this post: How to convert python timestamp string to epoch?

and I tried to use this code:

from datetime import datetime

p = '%Y-%m-%dT%H:%M:%S.%fZ'
mytime = "2009-03-08T00:27:31.807Z"
epoch = datetime(1970, 1, 1)
print((datetime.strptime(mytime, p) - epoch).total_seconds())

but the format of what I read is different, and it does not work... Any idea??

just_learning
  • 413
  • 2
  • 11
  • 24
  • 1
    See this answer: https://stackoverflow.com/a/38035785/397872 – Jacek Krysztofik Jan 08 '21 at 21:25
  • 1
    Normally Excel doesn't store a date as a string, it stores it as the number of days from the start of the epoch. Just multiplying by the number of seconds per day should get what you want. – Mark Ransom Jan 08 '21 at 21:34

1 Answers1

4

Utilizing the strftime() and strptime() Format Codes section from the docs, the following should work:

from datetime import datetime

date_time_str_excel = '2018-07-11T10:40:09+00:00'
date_time_obj = datetime.strptime(date_time_str_excel, '%Y-%m-%dT%H:%M:%S%z')
print(date_time_obj)
print(date_time_obj.timestamp())

Output:

2018-07-11 10:40:09+00:00
1531305609.0
Sash Sinha
  • 18,743
  • 3
  • 23
  • 40