Knowledge Base

Formatting Numbers and Dates

Product(s): Tableau Desktop
Version(s): All
Last Modified Date: 04 Feb 2016

 

The Tableau Formatting window lets you change fonts, add banded shading, and modify lines, colors, grids, numbers, and dates. This article focuses on some options for formatting numbers and dates. For example, suppose you want to show a measure with a dollar sign or use a custom date format. Or maybe you want to format a date that is aggregated to the M/D/Y level. With Tableau formatting options, you can get your numbers formatted just the way you want them, without writing a calculation.

Change a measure to currency

The view in the following image shows sales over time. Notice that the sales figures are not formatted as monetary values.

The following steps show how to format the numbers as currency.

Step 1

Right-click the Sales axis along the left side of the view and choose Format.

Step 2

On the Axis tab, under the Scale area, select the Numbers drop-down list, and then select one of the following:

  • Currency (Standard) to add a dollar sign and two decimal places to the figures.

  • Currency (Custom) to specify the number of decimal places, how to show negative values, the units, whether to include a prefix or suffix, and whether to include a separator character.

Specify a different locale for number formats

By default, Tableau uses your computer's Region and Language settings to format numbers. However, you can set a different locale in the Tableau Format window.

The following steps show how to set Swiss German currency, using the same view as in the previous section.

Step 1

Right-click the Sales axis and select Format.

Step 2

On the Axis tab, under the Scale area, select the Numbers drop-down list, and then select Currency (Standard).

Step 3

In the Locale drop-down list, items appear in a Language (Country) format. So for this example, select German (Switzerland).

The view updates to show the sales figures in Swiss Francs, formatted for German language.

Tip: You can change the default currency setting so that every time you drag the Sales measure to a view it shows the settings you want. In the Measures pane, right-click the Sales (or other monetary measure), and select Default Properties > Number Format, and then format the field as shown above.

Custom date formats

Using the same view as in the previous sections, you can format the date axis to show a custom format such as Sunday 1/1 or Aug 19.

Step 1

Right-click the the Order Date field on Columns and select Exact Date.

Step 2

Right-click the Order Date field again and select Format.

Step 3

You can specify a new date format using the Dates control in the Scale area of the Format window. Choose from a variety of formats or create a custom format. The table below describes the values you can use for custom date formats.

CharacterDescription
(:)Time separator. In some locales, a different character is used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator in formatted output is determined by your system settings.
(/)Date separator. In some locales, oa different character is used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your system settings.
cDisplay the date as ddddd and display the time as
ttttt, in that order. Display only date information if there is no fractional part to the date serial number; display only time information if there is no integer portion.
dDisplay the day as a number without a leading zero (1 31).
ddDisplay the day as a number with a leading zero (01 31).
dddDisplay the day as an abbreviation (Sun Sat).
ddddDisplay the day as a full name (Sunday Saturday).
dddddDisplay the date as a complete date (including day, month, and year), formatted according to your system's short date format setting. The default short date format is m/d/yy.
ddddddDisplay a date serial number as a complete date (including day, month, and year) formatted according to the long date setting recognized by your system. The default long date format is mmmm dd, yyyy.
aaaaThe same as dddd, only it's the localized version of the string.
wDisplay the day of the week as a number (1 for Sunday through 7 for Saturday).
wwDisplay the week of the year as a number (1 54).
mDisplay the month as a number without a leading zero (1 12). If m immediately follows h or hh, the minute rather than the month is displayed.
mmDisplay the month as a number with a leading zero (01 12). If m immediately follows h or hh, the minute rather than the month is displayed.
mmmDisplay the month as an abbreviation (Jan Dec).
mmmmDisplay the month as a full month name (January December).
ooooThe same as mmmm, but localized.
qDisplay the quarter of the year as a number (1 4).
yDisplay the day of the year as a number (1 366).
yyDisplay the year as a 2-digit number (00 99).
yyyyDisplay the year as a 4-digit number (100 9999).
hDisplay the hour as a number without leading zeros (0 23).
HhDisplay the hour as a number with leading zeros (00 23).
NDisplay the minute as a number without leading zeros (0 59).
NnDisplay the minute as a number with leading zeros (00 59).
SDisplay the second as a number without leading zeros (0 59).
SsDisplay the second as a number with leading zeros (00 59).
000Display milliseconds. Use a period character as a separator before specifying milliseconds.
t t t t tDisplay a time as a complete time (including hour, minute, and second), formatted using the time separator defined by the time format recognized by your system. A leading zero is displayed if the leading zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:mm:ss.
AM/PMUse the 12-hour clock and display an uppercase AM with any hour before noon; display an uppercase PM with any hour between noon and 11:59 P.M.
am/pmUse the 12-hour clock and display a lowercase AM with any hour before noon; display a lowercase PM with any hour between noon and 11:59 P.M.
A/PUse the 12-hour clock and display an uppercase A with any hour before noon; display an uppercase P with any hour between noon and 11:59 P.M.
a/pUse the 12-hour clock and display a lowercase A with any hour before noon; display a lowercase P with any hour between noon and 11:59 P.M.
AMPMUse the 12-hour clock and display the AM string literal as defined by your system with any hour before noon; display the PM string literal as defined by your system with any hour between noon and 11:59 P.M. AMPM can be either uppercase or lowercase, but the case of the string displayed matches the string as defined by your system settings. The default format is AM/PM.

Specifying a custom format yyyy-MM-dd HH:mm:ss.000 would produce dates such as 2015-05-10 11:22:16.543. Such a format might be appropriate for scientific data.

Specifying a format DDDD DD would produce dates that show the Weekday and the Day, as in the following image.

Formatting distinct dates

What if you wanted to keep the distinct headers and still use a custom date format? For example, the view below shows the same view as before but this time it shows the sales for each distinct Month, Day, and Year.

Step 1

Right-click the date field on the Columns shelf, and select More > Custom. In the detail drop-down list, select Month/Day/Year.

Did this article resolve the issue?    

Attachments

Search Knowledge Base