0

I'd like to convert a column to date. My source data is from an Excel which is already formatted to date data type. However, when pandas read my file the date columns are read as e.g.'44249'

I tried the following code

RPT["Pot"] =`RPT["Pot"].apply(lambda x: pd.to_datetime(x, format='%d%m%Y'))

but I got the error time data '44249' does not match format '%d%m%Y' (match).

I also tried this code:

RPT["PLANNED SUBMISSION DATE TO E&P"] = pd.to_datetime(RPT["PLANNED SUBMISSION DATE TO E&P"])

but the results were 1970-01-01 00:00:00.000044365, which inaccurate.

Can I anyone please help me?

FObersteiner
  • 22,500
  • 8
  • 42
  • 72
Sheryna
  • 77
  • 1
  • 10
  • No we cant. How would we? 44249 cant be put into a date according to the format string. What is your desired output? 4-42-42? 44-2-42? Neither is a valid date in any shape or form! to_datetime with a format string is the wrong way to go – Patrick Artner Dec 23 '21 at 10:39
  • Well yes, while reading dates from excel file this may occur! Have you tried fixing the column in excel file itself? Try modifying date from 2021-12-23 to `2021-12-23. This might help. – MHS Dec 23 '21 at 10:42
  • 1
    @PatrickArtner since the OP is using pandas, [Convert Excel style date with pandas](https://stackoverflow.com/q/38454403/10197418) would be a more appropriate dupe. – FObersteiner Dec 23 '21 at 11:02
  • @MrFuppes - added it. Since the PO uses a lambda to convert, both would work. Having something that works with a series is nice though. – Patrick Artner Dec 23 '21 at 11:15

0 Answers0