Format String

If the format you want to use cannot be created with the given settings, the custom format string allows you to create your own formats using a code explained in the examples below.

The special characters allow you to multiply, divide, separate numbers, etc. Other characters are printed out in the resulting data.

Custom Numeric Format Strings

Special characters:

Character	Description
0	Always returns a value for the position it is written in. If there is no number in its place in the data, 0 (zero) will be used.
#	Returns values if there are numbers in its place in the data. If used to the left of the decimal point, all digits are returned even if there is one # in the format string and three digits in the data. If used to the right of the decimal point, the same number of digits are returned as there are # to the right of the decimal point, and the number gets rounded up or down. See example below.
,	If used before a decimal point, divide the number in the data by 1000. Note: A difference from Excel is that Excel allows for "," as divider after the decimal point as well.
%	Multiplies the number by 100 and inserts a "%" in the number in the location it is written in the format string.
.	Decimal point. Note: If no decimal point is used and there are decimals in the value you apply the format string on, the value gets rounded up or down.
;	Used to divide a format string if different formats are to be used for positive numbers, negative numbers and 0 (zero). If no semicolon is used, the format string is used for all numbers. If one semicolon is used, it divides the format string like this: String for positive numbers and zero;String for negative numbers If two semicolons are used, they divide the format string like this: String for positive numbers;String for negative numbers;String for zero
\	If a "\" is added before a special character that character will not modify the number, the character will only be added to the value.

Examples:

Note: All these examples use the number 12345.67 as the value from the data.

Format string	Result
# ####	1 2346
#.#	12345.7
#.000	12345.670
#,.#	12.3
#,,.##	.01
#%	1234567%
#\%	12345.67%
$#	$12346
#.##E+0	1.23E+4
#.#;(#.#)	12345.7 Note: Had the number been negative, the result would be: (12345.7)
23	23

See literature about custom numeric format strings, for example, on MSDN, for more information.

Custom DateTime Format Strings

Below are some examples of custom format strings for datetime formats. See literature about custom datetime format strings, such as that on MSDN, for more information.

Note: If you want to use any of the custom date and time format specifiers alone in a format string (for example, to use the "d", "h", or "M" specifier by itself), you must either add a space before or after the specifier, or include a percent sign ("%") before the single custom date and time specifier, to avoid it being interpreted as a standard format string.

Character	Description
yy	Returns the year, measured as a number between 0 and 99.
yyyy	Returns the year as a four-digit number.
M	Returns the month, measured as a number between 1 and 12, with one or two digits depending on the value. This means that June will be written as '6', when this format string is applied (whereas November is written as '11').
MM	Returns the month with two digits, measured as a number between 1 and 12. This means that June will be written as '06', when this format string is applied.
MMM	Returns the abbreviated name of the month. For example, 'Jun'.
MMMM	Returns the full name of the month. For example, 'June'.
d	Returns the day of the month, measured as a number between 1 and 31, with one or two digits depending on the value. This means that the 6th of a month will be written as '6' (whereas the 11th is written as '11'), when this format string is applied.
dd	Returns the day of the month with two digits, measured as a number between 1 and 31. This means that the 6th of a month will be written as '06', when this format string is applied.
ddd	Returns the abbreviated name of the day of the week. For example, 'Fri'.
dddd	Returns the full name of the day of the week. For example, 'Friday'.
h	Returns the hour using a 12-hour clock, with one or two digits depending on the value. This means that if the time is 7:20:22, then the format 'h' will display the hour as '7', that is, using a single digit. If the time is 11:20:22, then 'h' returns 11.
hh	Returns the hour using a 12-hour clock which always uses two digits. This means that the time 7:20:22 will be written as '07:20:22' when the 'hh' format string is applied.
H	Returns the hour using a 24-hour clock, with one or two digits depending on the value. This means that if the time is 7:20:22, then the format 'H' will display the hour as '7', that is, using a single digit. If the time is 11:20:22, then 'H' returns 11. If the time is 20 minutes past seven in the evening, then 'H' returns 19:20:00.
HH	Returns the hour using a 24-hour clock which always uses two digits. This means that 6 o'clock in the morning will be written as '06' and 6 o'clock in the evening will be written as '18', when this format string is applied.
m	Returns the minute with one or two digits, depending on the value. This means that six minutes will be written as '6' (whereas 11 minutes are written as '11').
mm	Returns the minute with two digits. This means that six minutes will be written as '06', when this format string is applied.
s	Returns the second with one or two digits, depending on the value. This means that six seconds will be written as '6' (whereas 11 seconds are written as '11').
ss	Returns the second with two digits. This means that six seconds will be written as '06', when this format string is applied.
f	Returns the tenths of a second.
ff	Returns the hundredths of a second.
fff	Returns the milliseconds.
tt	Returns the AM/PM designator.
:	Returns the time separator.
/	Returns the date separator.

You can also add any custom string value, but if any of the specifier characters are included in the string, they need to be escaped by a backslash (\).

Examples:

Note: All the examples below use the following value from the data: Friday, October 16, 2009, at 25 minutes past three in the afternoon.

Format string	Result
dd\t\h o\f MMMM yyyy	16th of October 2009
MMM d yyyy, HH:mm	Oct 16 2009, 15:25
\year: YY, \mon\t\h: MM, \da\y: dd	year: 09, month: 10, day: 16
hh:mm tt	03:25 PM
m \minu\te\s pa\s\t h, MMM d	25 minutes past 3, Oct 16
%d	16

Custom TimeSpan Format Strings

There are five different data values included in the TimeSpan format: day, hour, minute, second and fractions of seconds. These can be combined to a suitable format using a format string built by the following specifier characters:

Character	Description
d	Returns the number of days.
h	Returns the number of hours with one or two digits, depending on the value. This means that six hours will be written as '6' (whereas 11 hours are written as '11') when this format string is applied.
hh	Returns the number of hours with two digits. This means that six hours will be written as '06', when this format string is applied.
m	Returns the number of minutes with one or two digits, depending on the value. This means that six minutes will be written as '6' (whereas 11 minutes are written as '11') when this format string is applied.
mm	Returns the number of minutes with two digits. This means that six minutes will be written as '06', when this format string is applied.
s	Returns the number of seconds with one or two digits, depending on the value. This means that six seconds will be written as '6' (whereas 11 seconds are written as '11') when this format string is applied.
ss	Returns the number of seconds with two digits. This means that six seconds will be written as '06', when this format string is applied.
f	Returns the fractions of seconds. When specifying custom format strings in Column Properties you can also add a number between 1 and 3 after the 'f', defining how many decimals will be shown. If no number has been specified, three numbers will be shown, if available.

Between each specifier character, you need to supply some kind of separator. This could be a custom string value, but if any of the specifier characters are included in the string, they need to be escaped by a backslash (\). You can also include an initial and a conclusive string.

Examples of custom format outputs for a TimeSpan column:

Note: All the examples below use the following value from the data: -5 days, 7 hours, 11 minutes, 3.1234 seconds.

Format string	Result
d.h:m:s.f	-5.7:11:3.1
d.hh:mm:ss.f2	-5.07:11:03.12
Ti\me\span i\s d \day\s	Timespan is -5 days
d \day\s h \hour\s m \minute\s s \secon\d\s	-5 days 7 hours 11 minutes 3 seconds
f s m h d	123 3 11 7 -5