How to Change Date Format in Google Sheets to DD/MM/YYYY When Dates are in Text Format?

Changing the format of dates in Google Sheets from text to a specific date format like DD/MM/YYYY can streamline data management and improve readability. Here’s a step-by-step guide to help you achieve this.

How to Change Date Format in Google Sheets to DD/MM/YYYY When Dates are in Text Format

Step 1: Prepare Your Google Sheet

Open your Google Sheet containing the date data formatted as text. Ensure you have editing permissions.

Step 2: Identify the Text Date Column

Locate the column that contains the dates written in text format. For example, the dates might be in a column labeled “Date” or “Entry Date”.

Step 3: Insert a New Column for the Reformatted Dates

Insert a new column next to the text date column to place the reformatted dates.

To insert a new column, right-click on the column header of the adjacent column and select “Insert 1 left” or “Insert 1 right” from the menu.

Step 4: Use the DATEVALUE, TEXT, MID, FIND, LEFT, RIGHT, and TRIM Functions to Extract and Reformat Dates

In the new column, use a combination of Google Sheets functions to extract the day, month, and year from the text and convert them into a date format.

Here’s a general formula you can use if your initial date format is something common, like MM/DD/YYYY or similar:

=TEXT(DATEVALUE(TRIM(MID(A2, FIND(" ", A2) + 1, 3)) & " " & LEFT(TRIM(LEFT(A2, FIND(",", A2) - 1)), IF(FIND(" ", A2) > 2, 2, 1)) & ", " & RIGHT(A2, 4)), "dd/mm/yyyy")

• Replace A2 with the cell reference that corresponds to your data.

Step 5: Drag Down the Formula

Fill down the formula to other cells in the new column by clicking on the small square at the bottom right corner of the cell with the formula and dragging it down through the column.

Step 6: Format the New Date Column

Once all dates are converted, ensure they display in DD/MM/YYYY format:

1. Select the new date column.
2. Go to Format > Number > Custom date and time.
3. Type dd/mm/yyyy into the custom format box and press “Apply”.

Step 7: Verify and Clean Up

Check a few entries randomly to ensure that dates are converted correctly. Replace or delete the original text date column if necessary, depending on your needs.

Conclusion

You now have successfully reformatted text-based dates into a standard date format in Google Sheets.

This approach not only improves the appearance of your dates but also makes it easier to perform date-related calculations and sorting.