Convert and hexadecimal string into a datetime result in python

Question

I have been trying to find a way to perform the following action.

I just received an excel file with the following formula.

  Row Value                   Excel formula                                               Result
 608332e6943e7263a56fb3ff   =(((HEX2DEC(LEFT("row value",8))/60)/60)/24)+DATE(1970,1,1)   4/23/2021  8:49:42 PM

I wondering if there is a way for me to achieve this same result in python. I tried the following but it did not work.

df_final = pd.read_csv("Book1.csv")
print(df_final)

                    Card ID  # of Members Created Date
0  608332c0806da55df13b498b             2     608332c0
1  60819c7ccd3a695b79f54f53             5     60819c7c
2  60817b9df8f5422bbaf2cff9             9     60817b9d
3  60806f3d24f11404f1470904             2     60806f3d
4  607ed78a89de73411e937655             1     607ed78a
5  608332e6943e7263a56fb3ff             2     608332e6
6               6.05364E+23             5     6.05364E
7               6.04084E+23             6     6.04084E
8               6.05395E+22             2     6.05395E
9               6.04716E+23             1     6.04716E

hexa = df_final["Created Date"]
df_final["Created Date"] = ((int(hexa,16)/60)/60)/24 + datetime.datetime(1970,1,1)
print(df_final)

I am looking to get an output like this:

                        Card ID  # of Members Created Date
0  608332c0806da55df13b498b             2     4/23/2021
1  60819c7ccd3a695b79f54f53             5     4/22/2021
2  60817b9df8f5422bbaf2cff9             9     4/22/2021
3  60806f3d24f11404f1470904             2     4/21/2021
4  607ed78a89de73411e937655             1     4/20/2021
5  608332e6943e7263a56fb3ff             2     4/23/2021
6               6.05364E+23             5     Invalid
7               6.04084E+23             6     Invalid
8               6.05395E+22             2     Invalid
9               6.04716E+23             1     Invalid

However, all I am getting is the following error:

df_final["Created Date"] = ((int(hexa,16)/60)/60)/24 + datetime.datetime(1970,1,1)
TypeError: int() can't convert non-string with explicit base

Thoughts about how to get this done? I appreciate any help! Joe.

---

Based on Otávio's response, I try to re-run but I am still not able to achieve what I am looking for.

While the exercise get the work done if there is just one string, it is not getting the job done when it comes multiple values. Here is the example:

df_final = pd.read_csv("Book1.csv")
df_final["Created Date"] = df_final["Card ID"].str[0:8]
print(df_final)
hex = df_final["Created Date"]
dtime_obj = datetime.datetime.utcfromtimestamp(int(hex, 16))
datetime_str = dtime_obj.strftime('%Y-%m-%dT%H:%M')

print(df_final)

                        Card ID  # of Members Created Date
0  608332c0806da55df13b498b             2     608332c0
1  60819c7ccd3a695b79f54f53             5     60819c7c
2  60817b9df8f5422bbaf2cff9             9     60817b9d
3  60806f3d24f11404f1470904             2     60806f3d
4  607ed78a89de73411e937655             1     607ed78a
5  608332e6943e7263a56fb3ff             2     608332e6
6               6.05364E+23             5     6.05364E
7               6.04084E+23             6     6.04084E
8               6.05395E+22             2     6.05395E
9               6.04716E+23             1     6.04716E
Traceback (most recent call last):
  File "c:\Users\JUB\Desktop\Manual Formulas 1\hexa.py", line 16, in <module>
    dtime_obj = datetime.datetime.utcfromtimestamp(int(hex, 16))
TypeError: int() can't convert non-string with explicit base

Thank you guys for helping me out! Joe.

Answer 1

Convert the hex string to an int, then this int to a datetime tuple:

import datetime
hex = '608332c0'
dtime_obj = datetime.datetime.utcfromtimestamp(int(hex, 16))
datetime_str = dtime_obj.strftime('%Y-%m-%dT%H:%M') # '2021-04-23T20:49:04Z'