Python Forum
Matplotlib: How do I convert Dates from Excel to use in Matplotlib
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Matplotlib: How do I convert Dates from Excel to use in Matplotlib
#1
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
Reply
#2
(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/...ate-system
https://docs.microsoft.com/en-us/office/...-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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [Solved] Matplotlib - Tricontour: how to set colorbar range ju21878436312 1 338 Dec-13-2021, 07:44 PM
Last Post: ju21878436312
  Matplotlib - close multple plots with user input Positron79 0 226 Dec-01-2021, 05:26 PM
Last Post: Positron79
  matplotlib x axis range goes over the set range Pedroski55 5 527 Nov-21-2021, 08:40 AM
Last Post: paul18fr
  plot on the same figure using a for loo with matplotlib drSlump 2 412 Oct-13-2021, 07:11 AM
Last Post: drSlump
  matplotlib, no plot when using Figure ajvperth 2 478 Oct-12-2021, 02:29 PM
Last Post: ajvperth
  Matplotlib Animation with Threading peterjv26 4 874 Oct-08-2021, 05:51 PM
Last Post: peterjv26
  Need to install an old version of matplotlib Pedroski55 3 688 Oct-02-2021, 07:36 AM
Last Post: buran
  How do I read in a Formula in Excel and convert it to do the computation in Python? JaneTan 2 600 Jul-07-2021, 02:06 PM
Last Post: Marbelous
  Trying to get base64Encoded Matplotlib Data Morkus 0 604 Jun-10-2021, 06:58 PM
Last Post: Morkus
  Filter Excel and Convert an Excel File giddyhead 0 900 May-13-2021, 06:31 PM
Last Post: giddyhead

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020