Excel dates turn into "yy-mm-dd hh:mm:ss" format after using read_excel and to_excel

Question

I have to get rid of duplicate rows on a *.xlsx file on a project. I have the code down here. But in the output file, date values turns into "yy-mm-dd hh:mm:ss" format after runnning my code. What would be the cause and solution to that wierd problem?

Running it on Pycharm 2019.2 Pro and Python 3.7.4

import pandas

mExcelFile = pandas.read_excel('Input/ogr.xlsx')
mExcelFile.drop_duplicates(subset=['FName', 'LName', 'Class', '_KDT'], inplace=True)
mExcelFile.to_excel('Output/NoDup.xlsx')

I'm expecting dates stay in original format which is "dd.mm.yy" but values become "yy-mm-dd hh:mm:ss"

Maybe you just have to make an Excel column wider? Probably Pandas turns date into datetime and it is wide to fit in regular column's width. It can be seen on your screenshot in formulas area. — crayxt
– crayxt, Commented Aug 31, 2019 at 13:06

crayxt · Accepted Answer · 2019-08-31 13:09:41Z

1

To control date format when writing to Excel, try this:

writer = pd.ExcelWriter(fileName, engine='xlsxwriter', datetime_format='dd/mm/yy')
df.to_excel(writer)

answered Aug 31, 2019 at 13:09

crayxt

2,4052 gold badges15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Estelle Arquette Over a year ago

Stil getting date formatted like this, 2018-01-01 00:00:00 insted of original format which is, 01.01.2018

gravlax Jan 21 at 10:27

With pandas=2.2.0, you should use the writer like this: with pd.ExcelWriter(path, datetime_format='dd/mm/yyyy') as writer: df.to_excel(writer)

Estelle Arquette · Accepted Answer · 2019-09-10 14:30:43Z

0

Actually answer from the link below solved it. Since I am new to python programming I didn't realize where the problem was. It was actually pandas converting cellvalues to datetimes. Detailed answer : https://stackoverflow.com/a/49159393/11584604

answered Sep 10, 2019 at 14:30

Estelle Arquette

113 bronze badges

Collectives™ on Stack Overflow

Excel dates turn into "yy-mm-dd hh:mm:ss" format after using read_excel and to_excel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related