Extracting a Date from String of Text

I need help extracting a date from a string of text. I tested each component (year, month, day) separate and get the right result. However, when I put the extraction formulas together in a DATE formula (in a Date column type) I get INVALID DATA TYPE.

Sample text string: 2025-10-20T23:26:16+03:00

Formulas that work individually:

Year—> =LEFT([Course completed]@row , 4),
- Year returns 2025 from sample string above
Month —> =LEFT(REPLACE([Course completed]@row , 1, 5, ""), 2)
- Month returns 10 from sample string above
Day —> =LEFT(REPLACE([Course completed]@row , 1, 8, ""), 2)
- Day returns 20 from sample string above

DATE formula with error:
=DATE(LEFT([Course completed]@row , 4), LEFT(REPLACE([Course completed]@row , 1, 5, ""), 2), LEFT(REPLACE([Course completed]@row , 1, 8, ""), 2))

Find more posts tagged with

Formulas

dates

Accepted answers

ECastellaw

I added the VALUE formula and my formula works now.
Before - =DATE(LEFT([Course completed]@row , 4), LEFT(REPLACE([Course completed]@row , 1, 5, ""), 2), LEFT(REPLACE([Course completed]@row , 1, 8, ""), 2))

After - =DATE(VALUE(LEFT([Course completed]@row , 4)), VALUE(LEFT(REPLACE([Course completed]@row , 1, 5, ""), 2)), VALUE(LEFT(REPLACE([Course completed]@row , 1, 8, ""), 2)))

All comments

Cayla Davis

Your formula looks close but few contents are missing, see example below

Extracting "MM/DD/YYYY" from a string like "Completion Date: MM/DD/YYYY" Assume your text string is in a column named [Text Column] and the date is always the last 10 characters in the format MM/DD/YYYY.Code

=DATE(VALUE(RIGHT([Text Column]@row , 4)), VALUE(MID([Text Column]@row , FIND("/", [Text Column]@row ) - 2, 2)), VALUE(MID([Text Column]@row , FIND("/", [Text Column]@row ) + 1, 2)))

Explanation of the Example Formula:

RIGHT([Text Column]@row , 4): Extracts the last four characters (the year).
FIND("/", [Text Column]@row ): Finds the position of the first slash.
FIND("/", [Text Column]@row , FIND("/", [Text Column]@row ) + 1): Finds the position of the second slash.
MID([Text Column]@row , FIND("/", [Text Column]@row ) - 2, 2): Extracts the two characters before the first slash (the month).
MID([Text Column]@row , FIND("/", [Text Column]@row ) + 1, 2): Extracts the two characters after the first slash (the day).
VALUE(): Converts the extracted text components into numerical values for the DATE function.

ECastellaw

After - =DATE(VALUE(LEFT([Course completed]@row , 4)), VALUE(LEFT(REPLACE([Course completed]@row , 1, 5, ""), 2)), VALUE(LEFT(REPLACE([Course completed]@row , 1, 8, ""), 2)))

Paul Newcome

@ECastellaw Glad you were able to get it figured out. In case you or anyone else was wondering, text based functions such as LEFT and RIGHT will output text strings. While these string may LOOK like numbers, they are stored on the back-end as text and are treated as such.

However, the DATE function requires numerical data to be entered which is where that VALUE function comes into play by converting the text strings into numbers.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.