---
title: "How to change the format of date columns - Cloud"
canonical: "https://support.appfire.com/space/TBL/74814374/How%20to%20change%20the%20format%20of%20date%20columns%20-%20Cloud"
format: markdown
---
> Macro (aura-html)

# <span style="color: #000000">Overview</span>

<span style="color: #000000">This example explains how to format the display of date columns without changing the actual data. </span><span style="color: #000000">This helps to keep the </span><span style="color: #000000">format </span><span style="color: #000000">consistent even if the format of the provided data is not. To do this, configure the appropriate columns</span><span style="color: #000000"> with the </span><span style="color: #000000">*M*</span><span style="color: #000000"> colum</span><span style="color: #000000">n type in the</span> *[Column types](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74814225)* <span style="color: #000000">parameter. </span>[Advanced date sorting](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74814618) <span style="color: #000000">specifies how to use the </span><span style="color: #000000">*M*</span><span style="color: #000000"> column type to customize how the column data is interpreted as data.</span>

<span style="color: #000000">In this page, two formats that support both interpreting the data and formatting the display (output) of the data are explained.</span>

- <u><span style="color: #000000">Input format</span></u><span style="color: #000000"> - This </span><span style="color: #000000">is the current</span><span style="color: #000000"> format specified on the </span><span style="color: #000000">*M*</span><span style="color: #000000"> column type; if nothing is specified, the default format is used. See</span> [Advanced date sorting](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74814618) <span style="color: #000000">for more details.</span>
- <u><span style="color: #000000">Display format</span></u><span style="color: #000000"> - This is how you want the date to be displayed and sorted. If not specified, the </span><span style="color: #000000">column is displayed in the same format as the input format</span><span style="color: #000000">. The display format is similar but, necessarily, more complex than the input format. The </span><span style="color: #000000">**~**</span><span style="color: #000000"> character is used to </span><span style="color: #000000">separate</span><span style="color: #000000"> the input and display formats to avoid most conflicts with characters used in formats. This is summarized in the succeeding sections.</span>

### <span style="color: #000000">Examples</span>

| <span style="color: #000000">**Column type**</span> | <span style="color: #000000">**Input format**</span> | <span style="color: #000000">**Display format**</span> |
| --- | --- | --- |
| <span style="color: #000000">M~YYYY.MM.DD</span> | <span style="color: #000000"><default></span> | <span style="color: #000000">YYYY.MM.DD</span> |
| <span style="color: #000000">MDDMMMYYYY~YYYY.MM.DD</span> | <span style="color: #000000">DDMMMYYYY</span> | <span style="color: #000000">YYYY.MM.DD</span> |
| <span style="color: #000000">M(de)DDMMMYYYY~YYYY.MM.DD</span> | <span style="color: #000000">(de)DDMMMYYYY</span> | <span style="color: #000000">YYYY.MM.DD</span> |
| <span style="color: #000000">MX~DD.MM.YY</span> | <span style="color: #000000">Unix timestamp</span> | <span style="color: #000000">DD.MM.YY</span> |
| <span style="color: #000000">MDD MMM YYYY~YYYY.MM.DD</span> | <span style="color: #000000">DD MMM YYYY</span><br><span style="color: #000000">Default Confluence Date Picker (US)</span> | <span style="color: #000000">YYYY.MM.DD</span> |

# <span style="color: #000000">Usage</span>

<span style="color: #000000">The formatting options listed on this page can be defined through the</span> <span style="color: #000000">*Column types*</span>* *<span style="color: #000000">parameter within the following macros:</span>

- [Table Plus macro](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74812542)
- [CSV (Comma Separated Values) macro](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74812384)
- [JSON Table Macro](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74812316)
- [Attachment Table macro](https://appfire.atlassian.net/wiki/spaces/TBL/pages/74812217)
- [SQL macro](https://appfire.atlassian.net/wiki/spaces/SQL/pages/172458339)
- [SQL File macro](https://appfire.atlassian.net/wiki/spaces/SQL/pages/172458378)
- [SQL Query macro](https://appfire.atlassian.net/wiki/spaces/SQL/pages/172458423)
- [Excel macro](https://appfire.atlassian.net/wiki/spaces/XL/pages/76613631)

### Screenshot

![Advanced Tables date column type format settings](media://b51ccd24-712b-45f2-88b1-7db53592fbba)

# Formatting options

> ℹ️ ### Acknowledgement
> ℹ️ 
> ℹ️ <span style="color: #000000">Advanced date sorting support with the </span><span style="color: #000000">*M*</span><span style="color: #000000"> column type is provided using Javascript</span> <span style="color: #000000">with the help of the</span> [Moment.js](http://momentjs.com/) <span style="color: #000000">library (open source, MIT license). A summary of the relevant parts of the</span> [Moment.js documentation](http://momentjs.com/docs/#/displaying/format/) <span style="color: #000000">is repeated here.</span>

|  | **Token** | **Output** |
| --- | --- | --- |
| **Month** | M | 1 2 ... 11 12 |
|  | Mo | 1st 2nd ... 11th 12th |
|  | MM | 01 02 ... 11 12 |
|  | MMM | Jan Feb ... Nov Dec |
|  | MMMM | January February ... November December |
| Quarter | Q | 1 2 3 4 |
| **Day of Month** | D | 1 2 ... 30 31 |
|  | Do | 1st 2nd ... 30th 31st |
|  | DD | 01 02 ... 30 31 |
| **Day of Year** | DDD | 1 2 ... 364 365 |
|  | DDDo | 1st 2nd ... 364th 365th |
|  | DDDD | 001 002 ... 364 365 |
| **Day of Week** | d | 0 1 ... 5 6 |
|  | do | 0th 1st ... 5th 6th |
|  | dd | Su Mo ... Fr Sa |
|  | ddd | Sun Mon ... Fri Sat |
|  | dddd | Sunday Monday ... Friday Saturday |
| **Day of Week (Locale)** | e | 0 1 ... 5 6 |
| **Day of Week (ISO)** | E | 1 2 ... 6 7 |
| **Week of Year** | w | 1 2 ... 52 53 |
|  | wo | 1st 2nd ... 52nd 53rd |
|  | ww | 01 02 ... 52 53 |
| **Week of Year (ISO)** | W | 1 2 ... 52 53 |
|  | Wo | 1st 2nd ... 52nd 53rd |
|  | WW | 01 02 ... 52 53 |
| **Year** | YY | 70 71 ... 29 30 |
|  | YYYY | 1970 1971 ... 2029 2030 |
| **Week Year** | gg | 70 71 ... 29 30 |
|  | gggg | 1970 1971 ... 2029 2030 |
| **Week Year (ISO)** | GG | 70 71 ... 29 30 |
|  | GGGG | 1970 1971 ... 2029 2030 |
| **AM/PM** | A | AM PM |
|  | a | am pm |
| **Hour** | H | 0 1 ... 22 23 |
|  | HH | 00 01 ... 22 23 |
|  | h | 1 2 ... 11 12 |
|  | hh | 01 02 ... 11 12 |
| **Minute** | m | 0 1 ... 58 59 |
|  | mm | 00 01 ... 58 59 |
| **Second** | s | 0 1 ... 58 59 |
|  | ss | 00 01 ... 58 59 |
| **Fractional Second** | S | 0 1 ... 8 9 |
|  | SS | 0 1 ... 98 99 |
|  | SSS | 0 1 ... 998 999 |
| **Timezone** | z or zz | EST CST ... MST PST   
**Note:** as of **1.6.0**, the z/zz format tokens have been deprecated. [Read more about it here](https://github.com/moment/moment/issues/162). |
|  | Z | -07:00 -06:00 ... +06:00 +07:00 |
|  | ZZ | -0700 -0600 ... +0600 +0700 |
| **Unix Timestamp** | X | 1360013296 |

# <span style="color: #000000">Internationalization</span>

<span style="color: #000000">See</span> [language support (i18n)](https://appfire.atlassian.net/wiki/display/TBL/Advanced+Date+Sorting#AdvancedDateSorting-Languagesupport(i18n))<span style="color: #000000">.</span>