1

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.

FDL
  • 115
  • 8
  • 1
    Excel stores its dates as a floating point number of days since 1899-12-30. – Mark Ransom Feb 24 '22 at 03:09
  • The example you gave does not match Excel's natural internal representation, so there's some missing information in your question. – Mark Ransom Feb 24 '22 at 03:17
  • @MarkRansom, sorry about it. I realized the time elapsed after I key in the values into the question, causing the values to be inaccurate. I have edited the question. Thank you. – FDL Feb 24 '22 at 03:20
  • The small amount of time elapsed while asking the question doesn't matter at all. The number you give is orders of magnitude too large. – Mark Ransom Feb 24 '22 at 03:22
  • Is this already answered [here](https://stackoverflow.com/questions/29387137/how-to-convert-a-given-ordinal-number-from-excel-to-a-date) and/or [here](https://stackoverflow.com/questions/31359150/convert-date-from-excel-in-number-format-to-date-format-python)? – ramzeek Feb 24 '22 at 03:23
  • Does this answer your question? [How do I create a datetime in Python from milliseconds?](https://stackoverflow.com/questions/748491/how-do-i-create-a-datetime-in-python-from-milliseconds) – FObersteiner Feb 25 '22 at 09:08

2 Answers2

3

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.

TheFaultInOurStars
  • 3,464
  • 1
  • 8
  • 29
  • It appears there's a time zone offset you need to account for too. – Mark Ransom Feb 24 '22 at 03:23
  • @MarkRansom Yeah, I was thinking the same. But since there I am not sure about the OP's time zone, I prefer to just mention the difference in the answer. – TheFaultInOurStars Feb 24 '22 at 03:25
  • Thanks. The timezone is in HongKong time. – FDL Feb 24 '22 at 03:31
  • @FDL Thanks. I have edited the question as per your comment. – TheFaultInOurStars Feb 24 '22 at 03:40
  • btw. I downvoted because the result ***just might happen to be correct***: if you use `.astimezone` on a naive datetime object, Python will assume that the represented date & time is *local time*. That can be pretty different depending on what machine you run this code on ... – FObersteiner Feb 25 '22 at 09:03
  • 1
    @FObersteiner Thank you for your note. Since you have answered(+1) the question based on what you said to me, I won't update the answer. I will take your tip into account in my future answers. Thanks again:) – TheFaultInOurStars Feb 25 '22 at 14:17
  • 1
    @AmirhosseinKiani thanks for coming back to this. Since people tend to just copy&paste code from SO to their own projects without actually reading the answers (+comments), you might want to change `datetime.utcfromtimestamp(float(dateString)/1000)` to `datetime.fromtimestamp(float(dateString)/1000, tz=tzGMT)`. Then your code is trap-free :) – FObersteiner Feb 25 '22 at 14:23
2

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
FObersteiner
  • 22,500
  • 8
  • 42
  • 72