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.
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.
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.
