libxlsxwriter
 All Data Structures Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
Working with Dates and Times

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:

#include "xlsxwriter.h"
int main() {
/* A number to display as a date. */
double number = 41333.5;
/* Create a new workbook and add a worksheet. */
lxw_workbook *workbook = workbook_new("date_and_times01.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
/* Add a format with date formatting. */
lxw_format *format = workbook_add_format(workbook);
format_set_num_format(format, "mmm d yyyy hh:mm AM/PM");
/* Widen the first column to make the text clearer. */
worksheet_set_column(worksheet, 0, 0, 20, NULL);
/* Write the number without formatting. */
worksheet_write_number(worksheet, 0, 0, number, NULL ); // 41333.5
/* Write the number with formatting. Note: the worksheet_write_datetime()
* function is preferable for writing dates and times. This is for
* demonstration purposes only.
*/
worksheet_write_number(worksheet, 1, 0, number, format); // Feb 28 2013 12:00 PM
return workbook_close(workbook);
}

date_example01.png

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:

// Date and time.
lxw_datetime datetime1 = {2014, 11, 25, 17, 45, 5.1};
// Date only.
lxw_datetime datetime2 = {2014, 11, 25, 0, 0, 0};
// Time only.
lxw_datetime datetime3 = {0, 0, 0, 17, 45, 5.1};

Using lxw_datetime and worksheet_write_datetime() the previous example can then be re-written as follows:

#include "xlsxwriter.h"
int main() {
/* A datetime to display. */
lxw_datetime datetime = {2013, 2, 28, 12, 0, 0.0};
/* Create a new workbook and add a worksheet. */
lxw_workbook *workbook = workbook_new("date_and_times02.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
/* Add a format with date formatting. */
lxw_format *format = workbook_add_format(workbook);
format_set_num_format(format, "mmm d yyyy hh:mm AM/PM");
/* Widen the first column to make the text clearer. */
worksheet_set_column(worksheet, 0, 0, 20, NULL);
/* Write the datetime without formatting. */
worksheet_write_datetime(worksheet, 0, 0, &datetime, NULL ); // 41333.5
/* Write the datetime with formatting. */
worksheet_write_datetime(worksheet, 1, 0, &datetime, format); // Feb 28 2013 12:00 PM
return workbook_close(workbook);
}

The output from this program is the same as the previous example.

date_example02.png

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:

#include "xlsxwriter.h"
int main() {
/* A datetime to display. */
lxw_datetime datetime = {2013, 1, 23, 12, 30, 5.123};
uint32_t row = 0;
uint16_t col = 0;
int i;
/* Examples date and time formats. In the output file compare how changing
* the format strings changes the appearance of the date.
*/
char *date_formats[] = {
"dd/mm/yy",
"mm/dd/yy",
"dd m yy",
"d mm yy",
"d mmm yy",
"d mmmm yy",
"d mmmm yyy",
"d mmmm yyyy",
"dd/mm/yy hh:mm",
"dd/mm/yy hh:mm:ss",
"dd/mm/yy hh:mm:ss.000",
"hh:mm",
"hh:mm:ss",
"hh:mm:ss.000",
};
/* Create a new workbook and add a worksheet. */
lxw_workbook *workbook = workbook_new("date_and_times03.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
/* Add a bold format. */
lxw_format *bold = workbook_add_format(workbook);
/* Write the column headers. */
worksheet_write_string(worksheet, row, col, "Formatted date", bold);
worksheet_write_string(worksheet, row, col + 1, "Format", bold);
/* Widen the first column to make the text clearer. */
worksheet_set_column(worksheet, 0, 1, 20, NULL);
/* Write the same date and time using each of the above formats. */
for (i = 0; i < 14; i++) {
row++;
/* Create a format for the date or time.*/
lxw_format *format = workbook_add_format(workbook);
format_set_num_format(format, date_formats[i]);
/* Write the datetime with each format. */
worksheet_write_datetime(worksheet, row, col, &datetime, format);
/* Also write the format string for comparison. */
worksheet_write_string(worksheet, row, col + 1, date_formats[i], NULL);
}
return workbook_close(workbook);
}

date_example03.png

Next: Working with Charts