Convert Date Format from Dot to Slash or Hyphen in Google Sheets

Published on

You can quickly convert date format from dot to slash using text and date functions in Google Sheets. Such converted date can be used in calculations then.

Google Sheets will recognize dates in which the date, month and year are separated either by slash or hyphen. But sometimes we have dates separated by the dot in Sheets due to the ignorance of our data entry operator.

The date separated by a dot will be treated as a text string. But you can convert a date separated by dot to a standard date separated by slash or hyphen in Google Sheets.

Date Separated by Dot: 26.11.2018 or 11.26.2018

(treated as a text string)

Date Separated by Slash: 26/11/2018 or 11/26/2018

(treated as a standard date format)

Date Separated by Hyphen: 26-11-2018 or 11-26-2018

(treated as a standard date format)

Let me share the formula with you that converts date format from dot to slash.

Note: Open my Google Sheets Functions guide in a new tab in case you want to refer any functions used in this tutorial.

How to Convert Date Format from Dot to Slash in Google Sheets

My Sheet is set to the date format DD/MM/YYYY. Your’s may be MM/DD/YYYY. Accordingly, there will be minor changes in the formula.

Date from Dot to Slash (DD/MM/YYYY)

For this example, the date in cell A1 is 26.11.2018.

Formula:

=date(right(A1,4),mid(A1,4,5),left(A1,2))

This formula can convert the dot date separator to slash thus convert the date as a text string to a standard date.

Date from Dot to Slash (MM/DD/YYYY)

For this example, the date in cell A1 is 11.26.2018.

Formula:

=date(right(A1,4),left(A1,2),mid(A1,4,5))

Use either of the above Date formulas to convert date format from dot to slash in Google Sheets.

To switch the date format from slash to hyphen in Google Sheets you must format the cell containing the date.

In my example, the date separated by a dot is in cell A1 and the formatted date is in cell B1. So select cell B1 and follow the below steps.

Go to Format > Number > More format > More date and time formats … There choose your desired date format.

change date format from slash to hyphen in Sheets

You can convert the above formulas to array formula. So that when you have a column of dates in dot-separated format, all that you can convert at one go.

How to Convert Date Separated by Dot to Slash/Hyphen in a Column

Assume the dates to convert from dot to slash are in the range A1: A10. Then use the below array formula in cell B1.

=ArrayFormula(date(right(A1:A10,4),mid(A1:A10,4,5),left(A1:A10,2)))

See the screen capture.

Google Sheets Formula to Convert Date Format from Dot to Slash

This is in DD/MM/YYYY format. If you want you can easily change the formula for MM/DD/YYYY format. To do that refer to my concerned non-array formula above.

Finally, some of you may want to apply the above formula that converts dates separated by dot to slash in an infinite range like A1: A.

Formula:

=ArrayFormula(TO_DATE(if(Len(A1:A),date(right(A1:A,4),mid(A1:A,4,5),left(A1:A,2)),)))

Enjoy!

More Resources:

1. Find the Past or Future Closest Date to Today in Google Sheets.

2. Populate a Full Month’s Dates Based on a Drop-down in Google Sheets.

3. Find Number of Months and Days between Two Dates in Google Sheets.

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.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.