How to: Specify Number or Date Format for Cell Content

  • 3 minutes to read

Date and Time Formats

This example demonstrates how to specify different display formats for date and time values in cells.

worksheet.Range["A1:F1"].Formula = "= Now()";

// Apply different date display formats.
worksheet.Cells["A1"].NumberFormat = "m/d/yy";

worksheet.Cells["B1"].NumberFormat = "d-mmm-yy";

worksheet.Cells["C1"].NumberFormat = "dddd";

// Apply different time display formats.
worksheet.Cells["D1"].NumberFormat = "m/d/yy h:mm";

worksheet.Cells["E1"].NumberFormat = "h:mm AM/PM";

worksheet.Cells["F1"].NumberFormat = "h:mm:ss";

Number Formats

This example demonstrates how to use different number formats to display numeric data using the most appropriate formats.

// Display 111 as 111.
worksheet.Cells["A1"].Value = 111;
worksheet.Cells["A1"].NumberFormat = "#####";

// Display 222 as 00222.
worksheet.Cells["B1"].Value = 222;
worksheet.Cells["B1"].NumberFormat = "00000";

// Display 12345678 as 12,345,678.
worksheet.Cells["C1"].Value = 12345678;
worksheet.Cells["C1"].NumberFormat = "#,#";

// Display .126 as 0.13.
worksheet.Cells["D1"].Value = .126;
worksheet.Cells["D1"].NumberFormat = "0.##";

// Display 74.4 as 74.400.
worksheet.Cells["E1"].Value = 74.4;
worksheet.Cells["E1"].NumberFormat = "##.000";

// Display 1.6 as 160.0%.
worksheet.Cells["F1"].Value = 1.6;
worksheet.Cells["F1"].NumberFormat = "0.0%";

// Display 4321 as $4,321.00.
worksheet.Cells["G1"].Value = 4321;
worksheet.Cells["G1"].NumberFormat = "$#,##0.00";

// Display 8.75 as 8 3/4.
worksheet.Cells["H1"].Value = 8.75;
worksheet.Cells["H1"].NumberFormat = "# ?/?";

Custom Number Formats

This example demonstrates how to create a custom number format to display positive numbers, negative numbers, zero and text values using different formats and colors. To do this, specify the appropriate code sections of the number format as required. These code sections are separated by semicolons and used in the following order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

// Set cell values.
worksheet["A2:B2"].Value = -15.50;
worksheet["A3:B3"].Value = 555;
worksheet["A4:B4"].Value = 0;
worksheet["A5:B5"].Value = "Name";

//Apply custom number format.
worksheet["B2:B5"].NumberFormat = "[Green]#.00;[Red]#.00;[Blue]0.00;[Cyan]\"product: \"@";