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?

Improve this question
4
  • 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 Commented Dec 23, 2021 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. Commented Dec 23, 2021 at 10:42
  • 1
    @PatrickArtner since the OP is using pandas, Convert Excel style date with pandas would be a more appropriate dupe. Commented Dec 23, 2021 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. Commented Dec 23, 2021 at 11:15
Related questions
13112
What does the "yield" keyword do in Python?
8107
Does Python have a ternary conditional operator?
3001
Convert string "Jun 1 2005 1:33PM" into datetime
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.