Converting Sharepoint Date Text field to Date format in List

FrancesH175 

Maybe SharePoint Online calculated fields do not support the MATCH function, im not sure. SharePoint's formula capabilities are limited compared to Excel, so some functions that work in Excel, like MATCH, maybe are not available in SharePoint calculated columns. Here's how you can rewrite the formula:

Updated Formula Using Nested IF Statements

=DATE(

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

    IF(MID([TextDate], 3, 3)="JAN", 1,

    IF(MID([TextDate], 3, 3)="FEB", 2,

    IF(MID([TextDate], 3, 3)="MAR", 3,

    IF(MID([TextDate], 3, 3)="APR", 4,

    IF(MID([TextDate], 3, 3)="MAY", 5,

    IF(MID([TextDate], 3, 3)="JUN", 6,

    IF(MID([TextDate], 3, 3)="JUL", 7,

    IF(MID([TextDate], 3, 3)="AUG", 8,

    IF(MID([TextDate], 3, 3)="SEP", 9,

    IF(MID([TextDate], 3, 3)="OCT", 10,

    IF(MID([TextDate], 3, 3)="NOV", 11,

    IF(MID([TextDate], 3, 3)="DEC", 12,

    0)))))))))))),

    VALUE(LEFT([TextDate], 2))

)

Important Considerations

• Data Consistency: Ensure all entries in your TextDate column strictly follow the DDMMMYYYY format, as any deviation will result in an error or incorrect conversion.
• Length of Formula: SharePoint calculated columns have a character limit (around 1,920 characters), and while this formula is well within that limit, more complex scenarios might require further simplification.

The MID function works in SharePoint Online and can be used in calculated fields to parse and extract specific parts of text strings, such as month abbreviations.

My answers are voluntary and without guarantee!

Hope this will help you.