The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.

How to: Specify Number or Date Format for Cell Content

  • 3 min to read

You can specify how to display numeric values in cells by applying number formats. For example, a number can appear in a cell as a percentage, decimal, currency, accounting, date or time value. To apply a number format to a cell or cell range, assign the corresponding number format code to the Formatting.NumberFormat property of the cell or cell range object. This document provides examples on how to apply different number formats to display cell content as date and time and as percentage, currency and decimal values, and how to create custom number formats.

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";

The image below shows the different formats used to display date and time values in cells (the workbook is opened in Microsoft® Excel®).

Spreadsheet_NumberFormats_DateTime

Number Formats

This example demonstrates how to use different number formats to display numeric values in cells.

// 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 = "# ?/?";

The image below shows the different formats used to display numbers in cells (the workbook is opened in Microsoft® Excel®).

Spreadsheet_NumberFormats

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: \"@";

The image below shows how different values are displayed in cells when a custom number format is applied (the workbook is opened in Microsoft® Excel®).

Spreadsheet_NumberFormats_Custom

See Also