CODEWITHSUNDEEP
python-2.7pandas
A. McMaster
A. McMaster

Reputation: 303

PANDAS: When Writing To Excel Change to 1904 Date System

Hopefully this is a super easy question, but while writing to a book, it would simplify my work if I could have it set to a 1904 date upon creation. I am currently doing it with a Macro but is creating the issue of adding 4 years to all my date fields when I do it in that order.

Is it possible while setting up excel writer to have it auto create the book set to 1904?

Thank you!

Andy

Upvotes: 2

Views: 268

Answers (2)

jmcnamara
jmcnamara

Reputation: 41584

As Troy points out it can be done from XlsxWriter via the constructor. It is also possible to pass this parameter to the xlsxwriter engine in Pandas:

import pandas as pd
from datetime import date

df = pd.DataFrame({'Dates': [date(2018, 1, 1),
                             date(2018, 1, 2),
                             date(2018, 1, 3),
                             date(2018, 1, 4),
                             date(2018, 1, 5)],
                   })

writer = pd.ExcelWriter("pandas_example.xlsx",
                        engine='xlsxwriter',
                        options={'date_1904': True})

df.to_excel(writer, sheet_name='Sheet1')

Option in the output file:

enter image description here

See the Passing XlsxWriter constructor options to Pandas section of the XlsxWriter docs.

Upvotes: 2

Troy D
Troy D

Reputation: 2245

You can do it with xlsxwriter, but I don't think there's a direct way from pandas.

workbook = xlsxwriter.Workbook(filename, {'date_1904': True})

xlsxwriter.readthedocs.io/workbook.html

Upvotes: 2

Related Questions