How to normalize different date formats when importing in google sheets

Question

Background

The problem I'm solving is mentioned here in detail. Another glitch is that the spreadsheet I'm reading from has random changes to the date format like so:

10/3/2021   11/3/2021   12/3/2021   13-03-2021  14-03-2021

When I import these dates into another sheet, how can I do it so that the dates are normalized (ie are imported in the same format)?

B/c what's happening is that when I'm extracting these dates and I run a chart on them, the chart only reads the first format and ignores the rest:

Spreadsheet details

I get the spreadsheet by email as an .xlxs file. I open it and save it as a google spreadsheet, which has locale as the United States.

I made a copy of the spreadsheet here.

What I tried

I simply tried applying a date format of DD/MM/YYYY on the cells with the different date format but that's not working. What's weird is that in the sheet I'm importing from, it's giving me a different date format than what appears:

Right now all I'm doing is manually changing dates from the DD-MM-YYYY format to DD/MM/YYYY Format.

I also tried converting the dates using this formula

=to_date(int(A1))

but it worked with some cells not others

Answer 1

I wrote this regex script to pick up on the different date formats:

day

=REGEXEXTRACT(to_text($A37),"(.{1,2})[/-]")

month

=REGEXEXTRACT(to_text($A37),"[/-](.{1,2})[/-]")

this works with both the DD/MM/YYYY & DD-MM-YYYY formats

Answer 2

Please bear in mind that the basic data types in spreadsheets are number, text and formula. Dates, times, durations are automatically converted to serial-numbers using the spreadsheet locale to decide what part of the entered values is a year, month, date, hour, minute, second or millisecond.

The data shown in the screenshots and the demo spreadsheet are using an ambiguous date format. Apparently the intended format is DD-MM-YYYY but spreadsheets using United States as the locale parse these values as is their format were MM-DD-YYYY. As 13-3-2021 and following dates can't and be parsed automatically as date by the spreadsheet, they are parsed as text. Please bear in mind that spreadsheets store dates as serial-numbers.

There are several ways to fix this.

The simple way, is to grab bottom-right corner of the cell the March 1, 2021, which ever format is being used, and drag it to the right or to the bottom until the range is filled with dates from March 1, 2021 to March 21, 2021.

Another way, assuming that you prefer to use formulas and that you aren't able to work on the source of the data to avoid the use of ambiguous dates, you could use Google Sheets built-in functions, i.e,

• TO_TEXT(value) to convert the displayed values into text values. Required to fix the wrongly parsed dates as MM-DD-YYYY.
• LEFT, RIGHT, MID, among other built-in functions to take each part of the date as Year, Month and Day and pass those parts to DATE(year, month, date) to get the value being converted to the correct serial-number.

Another option is to use Google Apps Script.

Related

• In Google Sheets, how do I change a string which encodes a date into a date?

Also related

• Formatting Google Sheet Dates Inconsistent
• Changing a date in google sheets from MM/DD to DD/MM without changing the values
• Google Sheets interpreting dates from CSV in a weird way
• What date formats does Google Sheets recognize?