0

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)?

enter image description here

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:

enter image description here

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:

enter image description here

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

enter image description here

9
  • Please add more details including how is the data entered in the spreadsheet, the spreadsheet locale, show what you tried and a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Mar 30, 2021 at 19:21
  • 1
    ok @Rubén I updated my question to address your points, I'll take a look at your related answers
    – abbood
    Mar 30, 2021 at 19:33
  • ok @Rubén I looked at all the links you sent, I think my situation is a bit unique b/c I'm the only one converting this from an .xlsx file
    – abbood
    Mar 30, 2021 at 19:49
  • Please bear in mind that the use ambiguous date formats is present. In certain locales 12-3-2021 means December 3, 2021 while in others means March 12, 2021. Unites States use MM/DD/YYYY so in Excel and Google Sheets 13-3-2021 is considered a text value, not a date as a year only have 12 months.
    – Rubén
    Mar 30, 2021 at 19:52

2 Answers 2

2

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

1
  • I'm wondering if, from a pragmatic point of view, be easier to just do a drag & drop.
    – Rubén
    Apr 2, 2021 at 20:21
1

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

Also related

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.