[GH-ISSUE #200] Issue setting Date Number Format Cell #163

New issue

Closed

opened 2026-05-05 11:49:15 -06:00 by gitea-mirror · 3 comments

gitea-mirror commented

2026-05-05 11:49:15 -06:00

Owner

Originally created by @bettyleung on GitHub (Oct 8, 2018).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/200

Originally assigned to: @jmcnamara on GitHub.

Hi,

I am using libxlsxwriter to format a date such as "mm-dd-yyyy"but the number format is set to "Custom" instead of "Short Date". I am using clang version 1000.11.45.2, OS = OSX and libxlsxwriter 0.8.3. Is there a way to create a cell entry in excel with the number format set to "Short Date"?'

Excel information about number format.

Originally created by @bettyleung on GitHub (Oct 8, 2018). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/200 Originally assigned to: @jmcnamara on GitHub. Hi, I am using libxlsxwriter to format a date such as "mm-dd-yyyy"but the number format is set to "Custom" instead of "Short Date". I am using clang version 1000.11.45.2, OS = OSX and libxlsxwriter 0.8.3. Is there a way to create a cell entry in excel with the number format set to "Short Date"?' [Excel information about number format.]( https://support.office.com/en-us/article/display-dates-times-currency-fractions-or-percentages-f260abfb-5223-431c-b2ea-cd30bd1c7a55)

gitea-mirror

2026-05-05 11:49:15 -06:00

closed this issue
added the
question
label

gitea-mirror commented

2026-05-05 11:49:22 -06:00

Author

Owner

@jmcnamara commented on GitHub (Oct 8, 2018):

In order to get a "Short Date" you need to match the format used on your system. You can do that by setting the short date (or other format) for the cell and then switch to "Custom" to see what format Excel actually uses.

On one OS and Excel version that I have to hand it is dd/mm/yy and on another it isdd/mm/yyyy.

For the first case:

#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("short_date.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, "dd/mm/yy");

    /* Widen the first column to make the text clearer. */
    worksheet_set_column(worksheet, 0, 0, 20, NULL);

    /* Write a date. */
    worksheet_write_number(worksheet, 0, 0, number, format);

    return workbook_close(workbook);
}

Which gives:

You can see the the asterisks next to the date indicates that it is a built-in style format.

Switching to Custom shows the format string used:

@jmcnamara commented on GitHub (Oct 8, 2018): In order to get a "Short Date" you need to match the format used on your system. You can do that by setting the short date (or other format) for the cell and then switch to "Custom" to see what format Excel actually uses. On one OS and Excel version that I have to hand it is `dd/mm/yy` and on another it is`dd/mm/yyyy`. For the first case: ```C #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("short_date.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, "dd/mm/yy"); /* Widen the first column to make the text clearer. */ worksheet_set_column(worksheet, 0, 0, 20, NULL); /* Write a date. */ worksheet_write_number(worksheet, 0, 0, number, format); return workbook_close(workbook); } ``` Which gives: ![screen shot 2018-10-08 at 22 59 49](https://user-images.githubusercontent.com/94267/46636076-9a374080-cb4e-11e8-90a9-c5179f8afa4f.png) You can see the the asterisks next to the date indicates that it is a built-in style format. Switching to Custom shows the format string used: ![screen shot 2018-10-08 at 23 00 52](https://user-images.githubusercontent.com/94267/46636075-999eaa00-cb4e-11e8-8580-d763e1d2fb18.png)

gitea-mirror commented

2026-05-05 11:49:24 -06:00

Author

Owner

@jmcnamara commented on GitHub (Oct 8, 2018):

Another, more portable method across Excel versions and systems is to use one of the Excel's built in number formats using format_set_num_format_index():

#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("short_date.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_index(format, 14);

    /* Widen the first column to make the text clearer. */
    worksheet_set_column(worksheet, 0, 0, 20, NULL);

    /* Write a date. */
    worksheet_write_number(worksheet, 0, 0, number, format);

    return workbook_close(workbook);
}

@jmcnamara commented on GitHub (Oct 8, 2018): Another, more portable method across Excel versions and systems is to use one of the Excel's built in number formats using [format_set_num_format_index()](https://libxlsxwriter.github.io/format_8h.html#a688aa42bcc703d17e125d9a34c721872): ```C #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("short_date.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_index(format, 14); /* Widen the first column to make the text clearer. */ worksheet_set_column(worksheet, 0, 0, 20, NULL); /* Write a date. */ worksheet_write_number(worksheet, 0, 0, number, format); return workbook_close(workbook); } ```

gitea-mirror commented

2026-05-05 11:49:27 -06:00

Author

Owner

@bettyleung commented on GitHub (Oct 9, 2018):

Thank you, it works.

@bettyleung commented on GitHub (Oct 9, 2018): Thank you, it works.