---
schema_version: "1"
id: abf02e915960b23ef03042f2fd4e3d515abb359fa5408698d55eba203f0cbb04
title: "Format Dates Transformation: Standardize the Date Format in a Column"
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_recipe_transformation_formatdates.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_transformation_formatdates.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 1ea7ffd0bea697d05665f359b9a7d6449f5ea988f0b4fb4b7ea24b75cda58ae6
description: In CRM Analytics, if a text column contains dates in different formats, use the Format Dates recipe transformation in a Data Prep recipe to standardize the format for all dates in the column. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures you can successfully convert the column type from text to date. Optionally, you can convert the column type from Text to Date.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# Format Dates Transformation: Standardize the Date Format in a Column

In CRM Analytics, if a text column contains dates in different formats, use the Format Dates recipe transformation in a Data Prep recipe to standardize the format for all dates in the column. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures you can successfully convert the column type from text to date. Optionally, you can convert the column type from Text to Date.

### Required Editions

## Format Dates Transformation: Standardize the Date Format in a Column

In CRM Analytics, if a text (dimension) column contains dates in different formats, use the Format Dates recipe transformation in a Data Prep recipe to standardize the format for all dates in the column. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures you can successfully convert the column type from text (dimension) to date. Optionally, you can convert the column type from Text to Date.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small permission">
        <colgroup>
          <col style="width:50%">
          <col style="width:50%">
        </colgroup>
        <thead class="slds-text-title_caps ">
          <tr class="">
            <th scope="colgroup" class="slds-cell-wrap" colspan="2" style="text-align:center;vertical-align:top;" id="d137970e44">User Permissions
              Needed</th>
          </tr>
        </thead>
        <tbody>
          <tr class="">
            <td data-label="User Permissions Needed" class="slds-cell-wrap" style="vertical-align:top;" headers="d137970e44 ">To create a recipe:</td>

            <td class="slds-cell-wrap" style="vertical-align:top;" headers="d137970e44 ">Edit Analytics Dataflows OR Edit Dataset Recipes</td>

          </tr>
        </tbody>
      </table>

1. In a Transform node of a Data Prep recipe, select the text (dimension) column that contains dates in the Preview tab.
2. In the Transform toolbar, click the Format Dates button (![Format Dates button](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_button_formatDates.png)).
3. Enter the original date formats in the selected column.
    
    ![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png)
    
    Note The order of the date formats in the Original Formats box matters. For example, if a date is 01/06/2011, the date format could be dd/MM/yyyy or MM/dd/yyyy. The Date Format transformation assumes the first matching format shown the Original Date Format box is the right one.
4. Select the desired date format in the Change To field.
5. To convert a text (dimension) column with dates to a Date column type, select **Convert column type to Date**.
6. Under Show Results In, select whether the new values appear in a new column and whether to keep the original column.
7. If you elect to create a column, set the label under Column Label.
8. Click **Apply** to add the transformation to the Transform node.
9. To view the Graph area, click the Collapse button (![Collapse button](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_button_collapse.png)).

When you run the recipe, the transformation replaces the dates in the existing column with dates in the specified format. If the date's original format can't be determined or the date doesn’t have a value for a date component, the transformation replaces the date with null. For example, if you standardize on the MM/dd/yyyy:hh:mm:sssz format and a date value doesn’t have the seconds date component (sssz), then the transformation replaces the date with null.