Custom Number Formats, Date & Time Formats in Excel & VBA; NumberFormat property
—————————————————————————
Contents:
Custom Number Formats in Excel / VBA
Excel Home Tab, Format Cells dialog box
Custom Date & Time Formats in Excel / VBA
Display Date Format in Days, Months and Years
Display Time Format as Hours, Minutes and Seconds
Examples of Date & Time Formats
—————————————————————————
Custom Number Formats in Excel / VBA
In excel, default format for a cell is “General”. When you first enter a number in a cell, Excel tries to guess an appropriate format, viz. if you type $5 in a cell, it will format the cell to Currency; if you type 5%, it will format to a percentage. However, many times you will need to select or add an appropriate format, especially if you want a customized display. It is important to note that formatting a cell only effects its display and does not change or effect its actual or true value (which is used by Excel for calculations) ie. if you select a formatted cell, the formula bar will display the value which was typed before it was formatted (Note: in a percentage format, if you type 0.75 in a cell, it is displayed as 75% after applying the format). In excel, by default, text is left aligned and non-text (viz. numbers, percentage, date/time, …) are right aligned, unless you specifically change this. We show how to create Custom Number Formats: (i) in Excel Spreadsheet, in the Format Cells dialog box; and (ii) in VBA, using the NumberFormat property.
Excel Home Tab, Format Cells dialog box
On the Home Tab, Format menu, click Format Cells (or Cells) and select the Number tab; OR On the Home tab, click the Dialog Box Launcher Button image next to Number. After this, in the Category list, select Custom, and all built-in and custom number formats will be displayed. In the “Type” box, type the number format code.
The built-in number formats cannot be changed or deleted. You can type in to add a custom number format, which will get added at the bottom of the list. You can delete a custom number format by clicking on the Delete button. If you edit a custom number format, the original is retained and the new format also gets added to the list. It is therefore advisable to delete the custom number formats which are not required.
Custom number formats are specific to the workbook they are created in and will be saved and available only in that specific workbook. You can save the original workbook and use the custom number formats in additional workbooks.
Example: Enter Format Code in Excel (Home Tab) Format Cells dialog box:
Select the cells / range in your worksheet in which to apply the format, and then in the “Type” box type the number format code. Note that the number format code is not required to be enclosed in double quotation marks like in VBA. Type below format code:
_(#,###.00_);[Red](#,###.00);0.00
Use the NumberFormat Property to set or return the number format for the specified object, in VBA. You can apply the NumberFormat property to set the format code of a Range object, CellFormat object, PivotField object (only for a data field), DataLabel object, DataLabels Object, and so on. Syntax: expression.NumberFormat. It is necessary to specify an expression, which returns an object (say, Range object) for which you want to set or return the format code. If the number format for all cells in the specified range is not the same, this property will return Null.
NumberFormat property sets the number format in VBA. Use this property to format numbers and how they appear in cells. You can define a number format by using the same format code strings as Microsoft Excel (in the Format Cells dialog box). The NumberFormat property uses different code strings than the Format function.
Examples of VBA Code, with NumberFormat property:
ActiveSheet.Columns(“D”).NumberFormat = “#,###.00;[Red](#,###.00);0.00”
ActiveSheet.Rows(2).NumberFormat = “hh:mm:ss.00”
ActiveSheet.Range(“A5”).NumberFormat = “General”
The three codes set the number format in the Active Worksheet, for Column D, Row 2 and Cell A5 respectively. Note that the number format code is required to be enclosed in double quotation marks unlike when you enter in Excel,Home Tab, Format Cells dialog box.
————————————————————————————————————————
| Format Character | Description | Format Code | Number | Formatted Display |
| 0 (zero) | Digit placeholder. If the number has lesser number of digits than zeros in the format code, the insignificant zeros are displayed. This means that the minimum number of digits are determined by the position of zero at the extreme left before decimal and position of zero at extreme right after decimal, in the format code. | |||
| If the number has more digits than zeros to the left of the decimal point in the format code, the extra digits are displayed. | ||||
| The number of zeros to the right of the decimal point in the format code, determine the round off digits. The “00” format code rounds off to the nearest digit preceding the decimal point. | ||||
| 0.00 | 5.6 | 5.60 | ||
| 00.00 | 5.6 | 05.60 | ||
| 0.0 | 5.6 | 5.6 | ||
| 0 | 0.6 | 1 | ||
| 00.00 | 456.789 | 456.79 | ||
| 00 | 45.445 | 45 | ||
| # (number character) | Digit placeholder. This follows the same rules as 0 (zero), except that, if the number has lesser number of digits than “#” characters in the format code, the insignificant zeros are NOT displayed, even though it may be the only digit. | |||
| If the number has more digits than # characters to the left of the decimal point in the format code, the extra digits are displayed. | ||||
| The number of # to the right of the decimal point in the format code, determine the round off digits. The “##” format code rounds off to the nearest digit preceding the decimal point. | ||||
| ###.## | 456.6 | 456.6 | ||
| # | 456.68 | 457 | ||
| Where x is a numerical digit => | (###) ### – #### | 123456xxxx | (123) 456 – xxxx | |
| ##.# | 456.689 | 456.7 | ||
| ## | 45.678 | 46 | ||
| ? (question mark) | Digit placeholder. This follows the same rules as 0 (zero), except that, the character “?” leaves (ie. adds) a space for insignificant zeros either side of the decimal, while character zero displays “0” for them. It is particulalry useful to align decimal points in a column – you can align numbers decimally by adding spaces on either side of a number as required . | |||
| If the number has more digits than ? characters to the left of the decimal point in the format code, the extra digits are displayed. | ||||
| The number of ? to the right of the decimal point in the format code, determine the round off digits. The “??” format code rounds off to the nearest digit preceding the decimal point. | ||||
| ???.?? | 45.64 | 45.64 | ||
| ???.?? | 5.6 | 5.6 | ||
| 000.00 | 45.64 | 045.64 | ||
| 000.00 | 5.6 | 005.60 | ||
| ??.? | 456.689 | 456.7 | ||
| ?? | 45.678 | 46 | ||
| . (period) | Decimal point. The decimal point (ie. the “.” character) in the format code determines the decimal place. | |||
| The number of Digit Placeholders to the right of the decimal point in the format code, determine the round off digits. The “00” or “##” or “??” format codes round off to the nearest digit preceding the decimal point. | ||||
| 00.000 | 456.123456 | 456.123 | ||
| 00.00 | 456.789567 | 456.79 | ||
| ##.# | 456.789567 | 456.8 | ||
| ##.# | 0.65 | .7 | ||
| , (comma) | Thousand separator and number scaling | |||
| Thousand separator: In the format code, placing comma between two digit placeholders and to the left of decimal, will act as a thousand separator. | ||||
| Number Scaling: If comma is placed to the immediate left of the decimal point (in the format code), the number is divided by 1,000, n number of times wherein n is the number of characters “,”. The format string “0,,” will scale down the number 100 million to 100 (divides 100 million by 1,000 * 1,000); and the format code “0,,,” will divide the number by 1,000 * 1,000 * 1,000. Note that this number scaling will not apply the thousand separator, which will have to be done separately after the number scaling. | ||||
| # | 123456 | 123456 | ||
| #,### | 123456 | 123,456 | ||
| #,#00 | 123456 | 123,456 | ||
| #,###.## | 4567.234 | 4,567.23 | ||
| 0, | 10000 | 10 | ||
| #,##0,, | 1000000 | 1 | ||
| #,###.#, | 12345.678 | 12.3 | ||
| % (percent sign) | Percentage indicator. Numbers are displayed as a percentage of 100 with this. The percent sign in the format code multiplies the number by 100, before it is formatted. The symbol “%” is displayed at the same position at which it is inserted in the format code. | |||
| #.0% | 0.00625 | .6% | ||
| 0.000% | 0.00625 | 0.625% | ||
| #.##% | 0.00625 | .63% | ||
| %# | 0.5625 | %56 | ||
| / (forward slash) | Fraction format. The forward slash displays number in a fraction format (ie. non-decimal format). The extent of accuracy of the fraction is determined by the number of digit placeholders on the right of the “/” character. | |||
| #/# | 1.237 | 5/4 | ||
| #/## | 1.237 | 47/38 | ||
| #/### | 1.237 | 1023/827 |
| Format Character | Description | Format Code | Number | Formatted Display |
| E+ E- e+ e- | Scientific notation (exponential format). “E+” and “E-” followed by atleast one “0” character (in format code), displays the number in scientific notation, with the character “E” mentioned between the number and the exponent. The number of “0” characters determine the minimum number of exponent digits. “E+” indicates that the sign character (plus or minus) will always precede the exponent, whereas “E-” indicates that the sign character (minus) precedes only negative exponents. | |||
| The E stands for exponent. To avoid writing extremely long numbers, use scientific notation (SN), ie. a numeric value containing the letter E followed by a number. To convert a SN number say 5.0E+3 to the actual number, move the decimal position 3 positions to the right OR multiply the number to the left of E by 10 raised to the powerof 3 viz. the actual number is 5000.
If the exponent is negative, like in 5.25E-3, move the decimal position 3 positions to the left OR multiply the number to the left of E by 10 raised to the power of -3 viz. the actual number is -0.00525. |
||||
| 0.00E+0 | 4560000 | 4.56E+6 | ||
| #0.0E+00 | 4560000 | 4.6E+06 | ||
| 0.###E+0 | 123.456 | 1.235E+2 | ||
| ##E+0 | 0.0001234 | 1E-4 | ||
| ##E-0 | 123456 | 12E4 | ||
| ##E-0 | 0.0001234 | 1E-4 | ||
| $ / + – ( ) space | Literal characters. These are displayed as literals (exactly as typed) as per their position in the format code. Use these to display currency, to differentiate positive and negative numbers and for a more user-friendly display. | |||
| $ +#,###.00 | 1456.7 | $ +1,456.70 | ||
| \ (backslash) | Backslash. Any character appearing after backslash (\) will display as a literal, even though it may be reserved as an operator (say, %). The number 0.75 with the format code #.00% will format to 75.00%, but with the format code #.00\% it will format to .75%, ie. format code will not use % as operator but as a literal. To display several characters as literals enclose these in double quotation marks (” “) or in case of a single character, precede it with a backslash (\). | |||
| On typing any of the characters { } = < > : ! ^ & ‘ ~ backslash (\) is automatically inserted before the character and it gets displayed as a literal. Hence these characters, alongwith the literal characters mentioned above, are displayed exactly as typed without the use of quotation marks or backslash (refer Table 1). | ||||
| #.00\% | 0.75 | .75% | ||
| #.00% | 0.75 | 75.00% | ||
| _ (underscore) | Add spaces. An underscore followed by a character in the format code, creates a space which is equivalent to the width of that character. A usual format is an underscore followed by left or right paranthesis viz. _( _), which aligns the decimal points of positive numbers with negative numbers that are enclosed in paranthesis. | #,###.00;[Red](#,###.00);0.00 | 567 | 567.00 |
| _(#,###.00_);[Red](#,###.00);0.00 | 567 | 567.00 | ||
| _(#,###.00_);[Red](#,###.00);0.00 | -567 | (567.00) | ||
| * (asterik) | Repeat characters. An asterik followed by a character in the format code, will repeat that character to fill the column width. Only one asterik can be included in a section. Use asterik to fill in dashes after a number, to fill in lead spaces before a number (which will right align it) or lead zeros before a number. | |||
| 0.0*- | 1.23 | 1.2——– | ||
| * 0.0 | 1.23 | 1.2 | ||
| *00.0 | 1.23 | 00000 1.2 | ||
| @ (at character) | Text placeholder. Insert the @ character in the text section (the last & fourth section), to display text which you type in a cell. If you want to always display a specified text in addition to the text you type in a cell, insert this ‘always display text’ within double quotation marks (” “) and insert the @ character in the text section viz. “Text”@. Text will not be displayed if the “@” character is not inserted in this section. | |||
| Text Section Format, in both Excel Format Cells dialog box and VBA with NumberFormat property: => | “[Blue]#,###.00; [Red](0.0#); [Green]0.00; [Magenta]@” | Jack | Jack | |
| Text Section Format, in Excel Format Cells dialog box: => | [Blue]#,###.00;[Red] (0.0#);[Green] 0.00;[Magenta] “Hello “@ | Jack | Hello Jack | |
| £ € ¥ | Currency symbols. In VBA, use the Chr function to insert currency symbols though their ANSI / ASCII code. Note that $ can be entered as a literal character, as mentioned above. | |||
| Enter Currency symbols, in VBA with NumberFormat property: => | Chr(163) & “0.00” | 123 | £123.00 | |
| Enter Currency symbols, in VBA with NumberFormat property: => | Chr(128) & “0.0” | 123 | €1.2 | |
| Enter Currency symbols, in VBA with NumberFormat property: => | “#,### ” & Chr(165) | 123 | 1.234 ¥ | |
| Enter Currency symbols, in both Excel Format Cells dialog box and VBA with NumberFormat property: “£0.00”, “€0.0”, “#,### ¥”, “£#,###”, … => | “£#,###” | 123 | £123 |
———————————————————————————————————————————————
———————————————————————————————————————————————
| Format Character | Description | Format Code | Number | Formatted Display |
| [Black] [White] [Red] [Green] [Blue] [Yellow] [Magenta] [Cyan] | Font color. Enter color name (viz. Red) or color index (viz. Color 3) in square brackets (in the format code) to determine font color. Enter color as the first item in the section. Supported colors are the first 8 colors in the palette: Black (Color 1), White (Color 2), Red (Color 3), Green (Color 4), Blue (Color 5), Yellow (Color 6), Magenta (Color 7), Cyan (Color 8) . | |||
| [Red]#0.0 | 456 | 456.0 | ||
| [Color 5]#0.0 | 456 | 456.0 |
| Format Character | Description | Format Code | Number | Formatted Display |
| ; (semicolon) | Section separator. There can be upto four sections of code in a custom number format, wherein each section is separated by a semicolon. These sections determine the display of positive numers, negative numbers, zero value and text, in that order. If only one section is specified in the format code, it applies to all the four sections; if two sections are specified, the first applies to positive and zero values and the second section applies to negative numbers. If all four sections are specified, only then is the text affected by the fourth section. | |||
| You can also skip a section and specify code for the following or preceding section, but then you must enter the ending semicolon for the skipped section. Skipping a section(s) will result in a blank display for that section. If all sections are skipped ie. only 3 semicolons are entered, will mean a blank display for all numbers & text. However, if the first 3 sections are skipped and the text section (fourth section) is entered with the @ character, will mean a text display for all numbers & text (ie. for all 4 sections). | ||||
| [Blue]#,###.00;[Red](0.0#);[Green]0.00;[Magenta]@ | 1456.7 | 1,456.70 | ||
| [Blue]#,###.00;[Red](0.0#);[Green]0.00;[Magenta]@ | -1456.7 | (1456.7) | ||
| [Blue]#,###.00;[Red](0.0#);[Green]0.00;[Magenta]@ | 0 | 0.00 | ||
| [Blue]#,###.00;[Red](0.0#);[Green]0.00;[Magenta]@ | hello | hello |
| Format Character | Description | Format Code | Number | Formatted Display |
| = < > <= >= <> | Specify Conditions. You have an option to specify conditions subject to which a number format is to be applied. The condition should be enclosed in square brackets and it consists of a comparison operator and a value. Comparison operators which can be used are: = < > <= >= <>. The first three sections in the number format are by default applied to positive, negative, and zero values. However you can specify upto two of your own conditions by using comparison operators. | |||
| [Green][>=70]0.00;[Blue][>=40]0.0;[Red]0;[Magenta]@ | 75 | 75.00 | ||
| [Green][>=70]0.00;[Blue][>=40]0.0;[Red]0;[Magenta]@ | 51.55 | 51.6 | ||
| [Green][>=70]0.00;[Blue][>=40]0.0;[Red]0;[Magenta]@ | 25.62 | 26 | ||
| [Green][>=70]0.00;[Blue][>=40]0.0;[Red]0;[Magenta]@ | hello | hello |
Custom Date & Time Formats in Excel / VBA
Display Date Format in Days, Months and Years
| Code | Description | Display |
| d | Day is displayed as a number, as one digit or as two digit. | 1-31 |
| dd | Day is displayed as a number, as two digit, with a leading zero where applicable. | 01-31 |
| ddd | Day is abbreviated to three letters, viz. Sunday is displayed as Sun. | Sun-Sat |
| dddd | Day is displayed in its full format, viz. Sunday is displayed as Sunday. | Sunday-Saturday |
| m | Month is displayed as a number, as one digit or as two digit. | 1-12 |
| mm | Month is displayed as a number, as two digit, with a leading zero where applicable. | 01-12 |
| mmm | Month name is abbreviated to three letters, viz. January is displayed as Jan. | Jan-Dec |
| mmmm | Month is displayed in its full name, viz. January is displayed as January. | January-December |
| mmmmm | Month is displayed as a single letter, viz. January is displayed as J. | J-D |
| yy | Year is displayed as a number in two digits, viz. last 2 digits of the year are displayed. | 00-99 |
| yyyy | Year is displayed as a number in four digits, viz. all digits of the year are displayed. | 1900-9999 |
Display Time Format as Hours, Minutes and Seconds
| Code | Description | Display |
| h | Hour is displayed as a number, as one digit or as two digit. | 0-23 |
| hh | Hour is displayed as a number, as two digit, with a leading zero where applicable. | 00-23 |
| [h] or [hh] | Elapsed time in hours is displayed. Format code “[h]:mm:ss” displays elapsed time in hours, minutes & seconds. Ex. Code “[h]:mm:ss” will display as => | 37:18:32 |
| m | Minute is displayed as a number, as one digit or as two digit. Note that the “m” code is also used for displaying month. To use this code as minute(s), it should appear immediately after the h or hh code or immediately before the s or ss code, such as “m:ss”. | 0-59 |
| mm | Minute is displayed as a number, as two digit, with a leading zero where applicable. Note that the “mm” code is also used for displaying month. To use this code as minute(s), it should appear immediately after the h or hh code or immediately before the s or ss code, such as “h:mm”. | 00-59 |
| [m] or [mm] | Elapsed time in minutes is displayed. Format code “[mm]:ss” displays elapsed time in minutes & seconds. Ex. Code “[mm]:ss” will display as => | 64:48 |
| s | Second is displayed as a number, as one digit or as two digit. Fractions of a second can be displayed by a format like “h:m:s.00”. | 0-59 |
| ss | Second is displayed as a number, as two digit, with a leading zero where applicable. Fractions of a second can be displayed by a format like “h:m:ss.00”. | 00-59 |
| [s] or [ss] | Elapsed time in seconds is displayed. Format code “[ss].00” displays elapsed time in seconds and its fraction(s). Ex. Code “[ss].00” will display as => | 1036.80 |
| AM/PM, am/pm, A/P, a/p | If these codes are included in the format, the hour is displayed using a 12-hour clock, else the hour is based on the 24-hour format. Display will include AM, am, A or a for a time before noon, and PM, pm, P or p for a time ‘from’ and ‘after’ noon. Ex. Code “hh:mm:ss AM/PM” will display as => | 01:27:50 AM |
Examples of Date & Time Formats
| Format Code | Display |
| mm/dd/yyyy | 01/02/2010 |
| m/d/yy | 1/2/10 |
| ddd, mmmm d, yyyy | Sat, January 2, 2010 |
| m/dd/yyyy hh:mm:ss.00 AM/PM | 5/15/2011 12:59:02.40 PM |
| h:mm:s | 1:17:2 |
| hh:mm:ss | 01:17:02 |