libxlsxwriter
|
Dates and times in Excel are represented by real numbers. For example a date that is displayed in Excel as "Jan 1 2013 12:00 PM" is stored as the number 41275.5.
The integer part of the number stores the number of days since the epoch, which is generally 1900, and the fractional part stores the percentage of the day.
A date or time in Excel is just like any other number. To display the number as a date you must apply an Excel number format to it. Here is an example:
Some options for creating or converting dates to the correct format are shown below.
To make working with dates and times a little easier the libxlsxwriter
library provides the lxw_datetime struct and the worksheet_write_datetime()
function.
The members of the lxw_datetime struct and the range of their values are:
Member | Value |
---|---|
year | 1900 - 9999 |
month | 1 - 12 |
day | 1 - 31 |
hour | 0 - 23 |
min | 0 - 59 |
sec | 0 - 59.999 |
Dates in Excel do not support timezones and the maximum resolution of times is milliseconds.
If dates or times are required without the other you should initialize the unrequired values to 0
:
Using lxw_datetime and worksheet_write_datetime() the previous example can then be re-written as follows:
The output from this program is the same as the previous example.
Another alternative when handling dates is Unix Time which is a common integer time format. It is defined as the number of seconds since the Unix epoch (1970-01-01 00:00 UTC).
The worksheet_write_unixtime()
function can be used to write dates and times in this format. Negative values can also be used for dates prior to 1970:
The output from this program is:
Dates can be formatted using any of the date formats supported by Excel. Here is a longer example that shows the same date in a several different formats:
To get date formats that show up in Excel as a "Date" or "Time" number category see Number Format Categories.
Next: Working with Charts