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:
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
but it worked with some cells not others