Formatting Numbers and Dates
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.
Right-click the Sales axis along the left side of the view and choose Format.
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.
Right-click the Sales axis and select Format.
On the Axis tab, under the Scale area, select the Numbers drop-down list, and then select Currency (Standard).
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
Right-click the the Order Date field on Columns and select Exact Date.
Right-click the Order Date field again and select Format.
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.
|(:)||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.|
|c||Display the date as |
|d||Display the day as a number without a leading zero (1 31).|
|dd||Display the day as a number with a leading zero (01 31).|
|ddd||Display the day as an abbreviation (Sun Sat).|
|dddd||Display the day as a full name (Sunday Saturday).|
|ddddd||Display 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 |
|dddddd||Display 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 |
|aaaa||The same as dddd, only it's the localized version of the string.|
|w||Display the day of the week as a number (1 for Sunday through 7 for Saturday).|
|ww||Display the week of the year as a number (1 54).|
|m||Display the month as a number without a leading zero (1 12). If |
|mm||Display the month as a number with a leading zero (01 12). If |
|mmm||Display the month as an abbreviation (Jan Dec).|
|mmmm||Display the month as a full month name (January December).|
|oooo||The same as mmmm, but localized.|
|q||Display the quarter of the year as a number (1 4).|
|y||Display the day of the year as a number (1 366).|
|yy||Display the year as a 2-digit number (00 99).|
|yyyy||Display the year as a 4-digit number (100 9999).|
|h||Display the hour as a number without leading zeros (0 23).|
|Hh||Display the hour as a number with leading zeros (00 23).|
|N||Display the minute as a number without leading zeros (0 59).|
|Nn||Display the minute as a number with leading zeros (00 59).|
|S||Display the second as a number without leading zeros (0 59).|
|Ss||Display the second as a number with leading zeros (00 59).|
|000||Display milliseconds. Use a period character as a separator before specifying milliseconds.|
|t t t t t||Display 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 |
|AM/PM||Use 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/pm||Use 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/P||Use 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/p||Use 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.|
|AMPM||Use 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.
Right-click the date field on the Columns shelf, and select More > Custom. In the detail drop-down list, select Month/Day/Year.