Failed to Convert Datetime-CodePudding

I am sorry to ask, but I tried a couple of days & failed to convert datetime in Python.

I have been trying to convert the 1st column (Date) into datetime format. Then minus 7 hours & 30 minutes as a new column named as 'PDN date'. Since there are bad data (e.g. string) in the 'Date' Column, I used " errors='coerce' " to convert bad data into NAN

Reference Picture

import datetime
Tonnage1["PDN Date"]=pd.to_datetime(Tonnage1["Date"], format='%d-%m-%y %H:%M:%S',errors='coerce') datetime.timedelta(hours=-7,minutes=-30)
Tonnage1.head()

Eventually, what i get is all NAT in 'PDN Date' column. What can I do? :'(

Appendix:

Here is the entire script:

*get files in google drive:

from google.colab import drive
drive.mount ('/content/drive')

*import everythings as usual:

import pandas as pd
import glob
import numpy as np
import os
import datetime
import openpyxl

*combine all files in google drive:

li=[]
os.chdir('/content/drive/MyDrive/Raw Data/')
allFiles=(glob.glob("*/*/*.xls"))

for file in allFiles:
  df=pd.read_excel(file,index_col=False)

  li.append(df)

Tonnage=pd.concat(li,axis=0,ignore_index=True)

Eliminate unnecessary columns:

Tonnage1=Tonnage.drop([  "Unnamed: 5","Unnamed: 6","Unnamed: 7","Unnamed: 8","Unnamed: 9","Unnamed: 10","Unnamed: 11","Unnamed: 12","Unnamed: 13","Unnamed: 14","Unnamed: 15","Unnamed: 16","Unnamed: 17"],axis=1)

*Rename headers

Tonnage1 = Tonnage1.rename(columns={'Unnamed: 0':'Date','Unnamed: 1':'formula','Unnamed: 2':'Customer','Unnamed: 3':'sortClass','Unnamed: 4':'weight'})

*Finally convert the date

 Tonnage1["PDN Date"]=pd.to_datetime(Tonnage1["Date"], format='%d-%m-%Y %H:%M:%S',errors='coerce') datetime.timedelta(hours=-7,minutes=-30)
    Tonnage1.head()

CodePudding user response：

You are close, change %y to %Y for match year in format YYYY and also / instead -:

Tonnage1["Date"]=pd.to_datetime(Tonnage1["Date"], 
                                format='%d/%m/%Y %H:%M:%S',
                                errors='coerce')