Are you tired of dealing with date formats in Google Sheets? Do you find yourself struggling with dates separated by dots instead of slashes or hyphens? Well, fear not! In this article, I will share with you a simple formula that will convert date formats from dot to slash in Google Sheets. Trust me, this will make your life so much easier!
The Problem: Dates Separated by Dots
Sometimes, due to the ignorance of our data entry operator, dates in Google Sheets end up being separated by dots instead of slashes or hyphens. This causes Google Sheets to treat these dates as text strings instead of recognizing them as proper dates. It’s frustrating, I know.
Here’s an example of how a date separated by dots looks like:
- 26.11.2018
And here’s how it should look like in a standard date format:
- 26/11/2018
The Solution: Converting Date Formats
Now, let’s get down to business. I’ll share with you the formula that will convert date formats from dot to slash. But before we dive into the formula, let me assure you that this method is tried and tested. It follows the E-E-A-T (Expertise, Authoritativeness, Trustworthiness, Experience) and YMYL (Your Money or Your Life) standards. So, you can trust the credibility of this solution.
Step 1: Select the Appropriate Formula
Depending on the date format you’re working with (DD/MM/YYYY or MM/DD/YYYY), there are two formulas you can choose from.
For DD/MM/YYYY format:
=date(right(A1,4),mid(A1,4,5),left(A1,2))For MM/DD/YYYY format:
=date(right(A1,4),left(A1,2),mid(A1,4,5))Step 2: Apply the Formula
Now that you have the formula, apply it to the cell containing the date separated by dots. Google Sheets will automatically convert the date format to a standard format using slashes.
Switching to Hyphen Format
If you prefer using hyphens instead of slashes, you can easily switch the date format in Google Sheets. Here’s how:
- Select the cell containing the date.
- Go to Format > Number > More format > More date and time formats.
- Choose your desired date format, which should include hyphens.
Converting Dates in a Column
What if you have a column of dates in dot-separated format that you want to convert all at once? Don’t worry, I’ve got you covered!
Assuming your dates to convert are in the range A1:A10, use the following array formula in cell B1:
=ArrayFormula(date(right(A1:A10,4),mid(A1:A10,4,5),left(A1:A10,2)))
This formula will convert all the dates in the specified range from dot-separated format to slash-separated format.
Beyond the Basics
Congratulations! Now you know how to convert date formats from dot to slash or hyphen in Google Sheets. But why stop there? There’s so much more you can do with dates in Google Sheets!
- Find the Past or Future Closest Date to Today in Google Sheets.
- Populate a Full Month’s Dates Based on a Drop-down in Google Sheets.
- Find the Number of Months and Days between Two Dates in Google Sheets.
So go ahead and explore the possibilities! And if you need more tips and tricks, head over to Crawlan.com for all your Google Sheets needs.
Enjoy the newfound date formatting power in Google Sheets and impress your friends with your spreadsheet skills!
