Convert Text Dates with Dots to Slash Format in Google Sheets

Published on

If you have a date separated by dots that is not recognized as a valid date by the ISDATE function, you may need to convert it before using it for assignments, comparisons, or summarizing by date, month, etc. You can convert text-formatted dates with dots to a slash format, which Google Sheets recognizes as a valid date.

Generic Formula

=LET(seg, SPLIT(A2, "."), DATE(CHOOSECOLS(seg, 3), CHOOSECOLS(seg, 2), CHOOSECOLS(seg, 1)))

Where:

  • A2 contains the text-formatted date separated by dots.
  • SPLIT(A2, ".") divides the date into day, month, and year components.
  • CHOOSECOLS(seg, 3), CHOOSECOLS(seg, 2), CHOOSECOLS(seg, 1) extract the year, month, and day, respectively, to construct a valid date using the DATE function.

The above formula assumes the date in A2 is in dd.mm.yyyy format. If the date follows the yyyy.mm.dd format, use the following formula instead:

=LET(seg, SPLIT(A2, "."), DATE(CHOOSECOLS(seg, 1), CHOOSECOLS(seg, 2), CHOOSECOLS(seg, 3)))

Example of Converting Text Dates with Dots to Slash Format in Google Sheets

Suppose we have text dates separated by dots in column A2:A.

Example of converting text-formatted dates with dots to a valid date format using slashes in Google Sheets

To convert them to a slash-formatted date, use the following formula in cell B2 and drag it down:

=LET(seg, SPLIT(A2, "."), DATE(CHOOSECOLS(seg, 3), CHOOSECOLS(seg, 2), CHOOSECOLS(seg, 1)))
  • The SPLIT function splits the text date in A2 using a . (period) as the delimiter.
  • The CHOOSECOLS function extracts each segment from the split result and arranges them in the correct order within the DATE function, which follows the syntax: DATE(year, month, day)

The output will be a valid date. If you are not satisfied with the default format, you can modify it by navigating to Format > Number > Custom date and time.

Converting Text Dates with Dots to Slash in a Column

To apply the formula to an entire column, replace A2 with A2:A and enter it as an array formula. However, this may return #VALUE! errors in rows corresponding to empty cells.

You might consider using IFERROR to suppress these errors, but doing so alone will remove the date formatting, returning only date values. To retain the date format, wrap IFERROR with TO_DATE as follows:

=ArrayFormula(TO_DATE(IFERROR(LET(seg, SPLIT(A2:A, "."), DATE(CHOOSECOLS(seg, 3), CHOOSECOLS(seg, 2), CHOOSECOLS(seg, 1))))))

Alternative Formula Using REGEXEXTRACT and DATE Combination

Instead of splitting at a specific delimiter, we can extract the day, month, and year components separately using REGEXEXTRACT:

Regex Patterns:

  • ^(\d+)\.: Captures one or more digits before the first dot.
  • \.(\d+)\.: Captures one or more digits between two dots.
  • \.(\d+)$: Captures one or more digits after the last dot.

So, the formula to convert text-formatted dates with dots to slash format will be:

=DATE(REGEXEXTRACT(A2, "\.(\d+)$"), REGEXEXTRACT(A2, "\.(\d+)\."), REGEXEXTRACT(A2, "^(\d+)\."))

If the date is in yyyy.mm.dd format, use:

=DATE(REGEXEXTRACT(A2, "^(\d+)\."), REGEXEXTRACT(A2, "\.(\d+)\."), REGEXEXTRACT(A2, "\.(\d+)$"))

To apply this formula to an entire column, use:

=ArrayFormula(TO_DATE(IFNA(DATE(REGEXEXTRACT(A2:A, "\.(\d+)$"), REGEXEXTRACT(A2:A, "\.(\d+)\."), REGEXEXTRACT(A2:A, "^(\d+)\.")))))

Explanation:

  • IFNA handles empty cells by preventing #N/A errors.
  • TO_DATE ensures the output remains in date format.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.