Converting Sharepoint Date Text field to Date format in List

FrancesH175 

To convert a text date in the format 23AUG2024 (Day-Month-Year) to a date format in a SharePoint list using a calculated field, you'll need to parse the text and convert it into a recognizable date format. SharePoint's calculated columns can handle this conversion with a formula, but it requires breaking down the text and rearranging the date components.

Here's how to achieve this:

Steps to Convert Date Text Field to Date Format

1. Assume the following setup:
• Text Column Name: TextDate (containing dates in the format 23AUG2024).
2. Create a Calculated Column:
• Go to your SharePoint list settings.
• Click on Create Column.
• Name your column (e.g., ConvertedDate).
• Set the column type to Calculated (calculation based on other columns).
• Choose Date and Time for the data type returned from this formula.
3. Enter the Formula:

The formula will parse the text date, convert it to a recognizable date format, and then output it as a date value. Here's the formula you can use:

Excel Code

=DATE(

    VALUE(RIGHT([TextDate], 4)),

    MATCH(MID([TextDate], 3, 3), {"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"}, 0),

    LEFT([TextDate], 2)

)

Explanation of the Formula:

• RIGHT([TextDate], 4): Extracts the year from the text date.
• MID([TextDate], 3, 3): Extracts the month abbreviation from the text date.
• MATCH(MID([TextDate], 3, 3), {"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"}, 0): Converts the month abbreviation into a month number. The MATCH function finds the position of the month abbreviation in the array of month names, which corresponds to the month number.
• LEFT([TextDate], 2): Extracts the day from the text date.
• DATE(year, month, day): Combines the extracted year, month, and day into a date format.
1. Save the Column:
• After entering the formula, click OK to save the calculated column.

Important Considerations

• Date Format Consistency: Ensure that all dates in your TextDate column follow the exact DDMMMYYYY format. Any deviations will cause the formula to fail.
• Formula Limits: SharePoint calculated columns have limitations on formula complexity and length. The provided formula should be well within these limits.
• Column References: Adjust [TextDate] in the formula to match the exact name of your text date column.

Example Formula Breakdown

For a date in 23AUG2024:

• LEFT([TextDate], 2) will extract 23 (day).
• MID([TextDate], 3, 3) will extract AUG (month).
• RIGHT([TextDate], 4) will extract 2024 (year).

The formula will then convert this into 23-Aug-2024 as a SharePoint Date format.

Summary

By using the calculated column with the provided formula, you can convert a text date in the format 23AUG2024 to a SharePoint Date format. Ensure that the text dates are consistent and correctly formatted for accurate conversion. If needed, validate the results and adjust the formula based on your specific requirements. My knowledge of this topic is limited, the text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.