Number Formatting lets you change the appearance of a number, date or time. You can change the number of decimal places, add dollar or percentage signs or even comma separators.
Number Format Codes
Row64 uses Excel / Spreadsheet style format codes to simplify formatting and make it familiar and easy to learn.
Format codes indicate the number of decimal places, any prefix at the front and whether their are commas. The simplest way to use them is to look at the examples at the end of this doc and pick the one you want.
Where To Set Formatting
Spreadsheet Formatting is set by right clicking on cells or the Format Cells button on the top bar
Dataframe Formatting is set by right clicking on any column
Chart Formatting is set by clicking on the chart settings and opening the aspect of the chart you want to tune the number format on. Typically charts will have multiple places where format codes can be set.
Order of Formating
If the chart has formatting in the Settings it will use that. If there is no Settings formatting, then it will use the format from the input dataframe.
Number Format Examples
Spreadsheet, Chart & Dataframe Codes
| Input Number | Format Code | Output |
|---|---|---|
| 123456789 | 0.00 | 123456789.00 |
| 123456789 | $###0 | $123456789 |
| 123456789 | #,##0 | 123,456,789 |
| 123456789 | 0,000 | 123,456,789 |
| 123456789 | #.##0 | 123.456.789 |
| 123456789 | $#,##0.00 | $123,456,789.00 |
| 123456789 | ($#,##0.00) | ($123,456,789.00) |
| 123456789 | “test”$#,##0.00 | test$123,456,789.00 |
| 123456789 | #,##0.00 | 123,456,789.00 |
| 123456789 | #,##0.00" Units" | 123,456,789.00 Units |
| -123456789 | 0 | -123456789 |
| -123456789 | 0.00 | -123456789.00 |
| -123456789 | $#,##0 | -$123,456,789 |
| -123456789 | ($#,##0.00) | -($123,456,789.00) |
| -5.0001 | 0.00 | -5.00 |
| -5.0001 | $#,##0 | -$5 |
| -5.0001 | ($#,##0.00) | -($5.00) |
| 49 | #,###.00 | 49.00 |
Percentage
| Input Number | Format Code | Output |
|---|---|---|
| 123456789 | 0% | 12345678900% |
| 123456789 | 0.0% | 12345678900.0% |
| 123456789 | 0.00% | 12345678900.00% |
| 123456789 | 0.00000% | 12345678900.00000% |
Conditional Formatting - Negative Colored Red
| Input Number | Format Code | Output |
|---|---|---|
| 123456789 | 0;[Red]0 | 123456789 |
| 123456789 | 0.00;[Red]0.00 | 123456789.00 |
| 123456789 | #,##0.00;[Red]#,##0.00 | 123,456,789.00 |
| -123456789.756458 | (“$”#,##0.00);[Red](“$”#,##0.00) | ($123,456,789.76) |
Conditional Formatting - Negative In Parenthesis
| Input Number | Format Code | Output |
|---|---|---|
| -123456789.343 | $#,##0;($#,##0) | ($123,456,789) |
| -123456789.343 | “$”#,##0.00;(“$”#,##0.00) | ($123,456,789.34) |
| 123456789 | $#,##0.00;[Red]($#,##0.00) | ($123,456,789.00) |
Date & Time Format Examples
Datetime formats can consists of two parts: a date and a time. The format should include the the date first followed by the time. The date part can be in the order of [year, month, day] or [month, day, year]
The time part must be in the order of hour, minutes, seconds, fractional seconds.
Since the Dataframe is focused on speed it has fewer codes for converting dates. The Spreadsheet & Charts have the full set of codes since they are focused on visual communication
Date Code Syntax
| Code | Result | Details | Note |
|---|---|---|---|
| yy | year | 2 digit | |
| yyyy | year | 4 digit | |
| m | month | 1 digit - 2 if needed | |
| mm | month | 2 digit | |
| mmm | month | short form text | Spreadsheet & Charts Only |
| mmmm | month | text | Spreadsheet & Charts Only |
| d | day | 1 digit - 2 if needed | |
| dd | day | 2 digit | |
| ddd | day | short form text | Spreadsheet & Charts Only |
| dddd | day | text | Spreadsheet & Charts Only |
| h | 12-hour | ||
| H | 24-hour | ||
| m | minute | ||
| s | seconds | ||
| mss | milliseconds | ||
| uss | microseconds | ||
| nss | nanoseconds | ||
| ampm | am or pm | ||
| AMPM | AM or PM |
Examples: Spreadsheet, Charts & Dataframe Codes
| Format Code | Result | Note |
|---|---|---|
| m/d/yy | 1/1/20 | |
| mm/dd/yy | 01/01/20 | |
| m/d/yyyy | 1/1/2020 | |
| mm/d/yyyy | 01/1/2020 | |
| m-d-yy | 1-1-20 | |
| m-d-yyyy | 1-1-2020 | |
| mm-d-yyyy | 01-1-2020 | |
| m-d-yy | 1-1-20 | |
| m-d-yyyy | 1-1-2020 | |
| mm-d-yyyy | 01-1-2020 | |
| yyyy/m/d | 2023/1/1 | |
| m/d/yyyy h:m | 14/1/2023 10:45 | |
| m/d/yyyy h:m:s | 14/1/2023 10:45:00 | |
| m/d/yyyy h:m:s ampm | 14/1/2023 15:45:00 pm | |
| mmdyyyy H:mm:ss.mss | 2020-01-01 12:00:00.000 | milliseconds |
| mmdyyyy H:mm:ss.uss | 2020-01-01 12:00:00.000 | microseconds |
| mmdyyyy H:mm:ss.nss | 2020-01-01 12:00:00.000000000 | nanoseconds |
| m-d-yyyyTh:m:s.mss | 1-1-2020T06:00:23.45679 | milliseconds |
| m/d/yyyy h:m:s.mss AMPM | 1/1/23 10:45 AM | milliseconds |
| mn/dd/yyyy h:m:s.mss ampm | 01/01/23 10:45 ampm | milliseconds |
| mm/dd/yyyy h:m:s ampm | 01/01/23 10:45 PM | |
| d-m-yyyy H:m | 31-12-1990 18:30 | |
| d-m-yy H:m | 31-12-90 18:30 | |
| d/m/yyyy HH:mm:ss | 14/03/2006 13:27:54 | |
| mdyy | 1120 | |
| mdyyyy | 112020 | |
| mmdyyyy | 0112020 | |
| d/m/yyyy | 4/3/2006 | |
| dd/mm/yyyy | 04/03/2006 |
Examples: Spreadsheet & Charts Only
| Format Code | Result |
|---|---|
| ddd | Fri |
| dddd | Friday |
| mmm | Sept |
| mmmm | September |
| mmmm d - yyyy | September 1 - 2020 |
| dddd mmmm d - yyyy | Tuesday September 1 - 2020 |
| m/ddd/yyyy | 1/Wed/2025 |
| m/dddd/yyyy | 1/Wednesday/2025 |