+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Matplotlib: How do I convert Dates from Excel to use in Matplotlib (/thread-32858.html)
Matplotlib: How do I convert Dates from Excel to use in Matplotlib - JaneTan - Mar-11-2021
A typical date in Excel is d/m/yyyy (eg '6/3/1990').
I want to import a timeseries from excel to plot in Matplotlib.
I note for Python datetime module the date have to be in the format yyyy-mm-dd (eg 1990-03-06) before user can create a datetime object.
So how should I convert the format in Excel to the format required by Python?
Thank you
RE: Matplotlib: How do I convert Dates from Excel to use in Matplotlib - buran - Mar-11-2021
(Mar-11-2021, 03:28 AM)JaneTan Wrote: A typical date in Excel is d/m/yyyy (eg '6/3/1990').This is incorrect. You must distinguish between how dates are stored internally in excel and how they are displayed in cell.
Dates in excel are stored as float numbers representing days from 1 Jan 1900 (with know "bug"
that 1900 is considered leap year, for backward compatibility with Lotus1-2-3. this is bug will be taken care of by the library you use to read the excel file anyway) or from 01 Jan 1904. You can set which system from your excel options
The decimal part represents the time from the start of the day.
Then you can represent the date in cell anyway you want - long, short date, custom number format...
https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year
As far as I know, and always used, the default one is one starting from 01 Jan 1900. The use of 2 systems can cause problems when copy paste/link dates between books from different workbooks, so don't tinker with the options.
Alternatively, I read dates in excel can be stored in XLSX files as an ISO 8601 formatted string, but never looked deeper into it. Probably again some option/
(Mar-11-2021, 03:28 AM)JaneTan Wrote: I note for Python datetime module the date have to be in the format yyyy-mm-dd (eg 1990-03-06) before user can create a datetime object.
First of all, if data in excel are indeed datetime, they will/can be converted to python
datetime.datetime objects when you read the excel file.Second - you can create
datetime.datetime object from any [proeprly formatted] string by providing the correct format string or by other sources - e.g. unix timestamp, etc.(Mar-11-2021, 03:28 AM)JaneTan Wrote: So how should I convert the format in Excel to the format required by Python?
The best would be to convert the dates (assuming they are dates in excel) when reading the file. So what library do you use to work with Excel? Show your code in python tags.