CODEWITHSUNDEEP
pythonexceldatetime-format
FDL
FDL

Reputation: 115

Convert number in Excel time format to string equivalent in python

I extracted data from an Excel cell of time format m/d/yy h:mm with the number content '1645704206000'. When converted to its string equivalent which shows the time, it is 2/24/22 12:03.

How do I convert this number '1645704206000' to 2/24/22 12:03 using python?

The timezone is in GMT timezone.

I am using Python 3.9 and Excel 2019.

Upvotes: 1

Views: 432

Answers (2)

TheFaultInOurStars
TheFaultInOurStars

Reputation: 3608

Update

It seems using the utcfromtimestamp on the datetime function is erroneous(Thanks to @FObersteiner for the comment). You should use fromtimestamp instead. So you can use the below code instead of the code at the bottom. Note that, pytz is deprecated. It seems that zoneinfo can do pytz's job.

from datetime import datetime
import pytz
tzHong = pytz.timezone('Hongkong')
tzGMT = pytz.timezone('Etc/GMT')
dateString = "1645704206000"
date = datetime.fromtimestamp(float(dateString)/1000, tz=tzGMT)
print("Date in Hongkong: " + date.astimezone(tz=tzHong).strftime("%m-%d-%y %I:%M"))
print("Date in GMT: " + date.strftime("%m-%d-%y %I:%M"))

Old Version

It looks like there is a problem in the question related to Excel's natural internal representation. But if you are interested in converting timestamps (with milliseconds) into a string, human-readable date, you can use datetime module in python:

from datetime import datetime
import pytz
tzHong = pytz.timezone('Hongkong')
tzGMT = pytz.timezone('Etc/GMT')
dateString = "1645704206000"
date = datetime.utcfromtimestamp(float(dateString)/1000)
print("Date in Hongkong: " + date.astimezone(tz=tzHong).strftime("%m-%d-%y %I:%M"))
print("Date in GMT: " + date.astimezone(tz=tzGMT).strftime("%m-%d-%y %I:%M"))

Output

Date in Hongkong: 02-24-22 08:03
Date in GMT: 02-24-22 12:03

Also note that the date you are showing (2/24/22 12:03) is in the GMT timezone, not Hongkong. Both time zones are shown in the code above. Make sure which one works for your desired output. Also, the answer provided by this answer is in 12-hour clock format. If you are interested in 24-hour format, just change %I in the code above with %H.

Upvotes: 3

FObersteiner
FObersteiner

Reputation: 25584

up-to-date (Python 3.9+) version:

from datetime import datetime, timezone
from zoneinfo import ZoneInfo 
tz_HK = ZoneInfo('Asia/Hong_Kong') # 'Hongkong' just links to 'Asia/Hong_Kong'

unix_ms = "1645704206000"
dt_utc = datetime.fromtimestamp(float(unix_ms)/1000, tz=timezone.utc)
print("date/time in Hongkong: " + dt_utc.astimezone(tz=tz_HK).strftime("%m-%d-%y %I:%M"))
print("date/time in UTC: " + dt_utc.strftime("%m-%d-%y %I:%M"))

date/time in Hongkong: 02-24-22 08:03
date/time in UTC: 02-24-22 12:03

Upvotes: 2

Related Questions