3 ms·

Ever tried to normalize/parse a list of dates in Excel? It takes a formula a mile long, and I’ve never gotten it to work quite right. E.g. 01/15/2024 1/15/20

Ever tried to normalize/parse a list of dates in Excel? It takes a formula a mile long, and I’ve never gotten it to work quite right. E.g. 01/15/2024 1/15/2024 1/15/24 01/03/2024 1/3/2024 1/3/24 Unless you write something in VBscript or whatever Excel uses now, it’s a nightmare.

HaZeust 2y ago
I just do it in Google Sheets with Apps Scripts and then move on from there. function dateFix() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var dateRange = sheet.getRange("A:A"); var dateValues = dateRange.getValues(); for (var i = 0; i < dateValues.length; i++) { if (dateValues[i][0]) { var fixedDate = fixDate(dateValues[i][0]); if (fixedDate) { sheet.getRange(i + 1, 2).setValue(fixedDate); } } } } function fixDate(dateString) { var datePattern = /^(\d{1,2})\/(\d{1,2})\/(\d{2}|\d{4})$/; var match = dateString.match(datePattern); if (!match) { return null; } var month = match[1].padStart(2, '0'); var day = match[2].padStart(2, '0'); var year = match[3]; if (year.length === 2) { year = '20' + year; } return month + '/' + day + '/' + year; }
- unsupp0rted 2y ago
  This is what I'm saying. It takes a dozen LoC to do what Excel is theoretically built to do: format a column of dates "as date".
deleted 2y ago
[deleted]
hot_gril 2y ago
Does DATEVALUE not solve this?
- unsupp0rted 2y ago
  Nope. DATEVALUE fumbles this badly. It's precisely for parsing dates but on a column with 1-digit vs. 2-digit days/months and 2-digit vs. 4-digit years (all in d/m/y format, mind you), it fails in one instance or the other.
  - maxerickson 2y ago
    Text to columns with a date value? (it works fine if it's one columns) I expect it gets your example there right, but you may have other issues in mind that you didn't push into the example.
  - elmer007 2y ago
    I just put all 6 of the examples above into Excel as text, then used DATEVALUE to parse them. It worked correctly for all 6. However, in the immediate parent comment, you mention d/m/y format, which 3 of the examples above could not be, so perhaps the details of the issue you've encountered are more subtle (such as 2-digit years that require context to determine the century); but, at present, it looks like DATEVALUE handles this well.
  - m16ghost 2y ago
    > (all in d/m/y format, mind you) The examples you gave are in m/d/y format though, and DATEVALUE() parses your examples correctly into Jan 15th, 2024 and January 3rd, 2024. DATEVALUE() parses ambiguous short date formats (e.g. 1/3/24) using the short date format specified in the Region settings of Windows Control Panel. So if you want to parse d/m/y format, you can try changing the settings there.
SECProto 2y ago
If you type those into cells in excel, they all display the same. Excel doesn't store them as dates, it just displays them as such. If you format it as a generic number, the first three lines all show 45306, while the latter three lines show 45294 as the cell value.
- unsupp0rted 2y ago
  In my case, usually the column comes into Excel via copy/paste of a dozen mixed columns, or File-Open on a CSV. The workaround, at least for CSVs, is to do an import via "Get Data" -> "From Text (legacy)" and tag the relevant column as "date". This doesn't always work though.
aspenmayer 2y ago
I learned how to format cells in early 2000s or maybe even 90s on Excel, so I know this functionality has existed for a long time, and these instructions are valid all the way back to Excel 2016, and up to current Excel 365. On Excel desktop, you can even set your own custom date parsing format. I don't know if this will work for you, but it is built-in and supported using the GUI, no coding/scripting necessary. https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e https://support.microsoft.com/en-us/office/format-a-date-the...
zamadatix 2y ago
The main hangup is probably around what year "24" should auto convert to being ambiguous as that adds an extra IF() to handle converting to 2024 instead of 1924. Beyond that it should just be =TEXTSPLIT(A1, "/") and =DATE(D1, C1, B1). If I was going to drop my hackerman shades and pull up to an Excel code golf competition this is the "fewest total characters using a single cell" I could come up with: =REGEXREPLACE(A1 "^0?(\d{1,2})\/0?(\d{1,2})\/(..)?(\d{2})$", "20$3/$2/$1")
jazzyjackson 2y ago
yyyy-MM-dd HH:mm works best for me, stored in a text field so it doesn't get converted to that ungodly float-since-1899 format and rendered by reference via =TEXT but can still be sorted and compared alphabetically it is unforgivable that a program used for timeseries data can't interpret an ISO 8901 without third party macros

HaZeust 2y ago

I just do it in Google Sheets with Apps Scripts and then move on from there. function dateFix() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var dateRange = sheet.getRange("A:A"); var dateValues = dateRange.getValues(); for (var i = 0; i < dateValues.length; i++) { if (dateValues[i][0]) { var fixedDate = fixDate(dateValues[i][0]); if (fixedDate) { sheet.getRange(i + 1, 2).setValue(fixedDate); } } } } function fixDate(dateString) { var datePattern = /^(\d{1,2})\/(\d{1,2})\/(\d{2}|\d{4})$/; var match = dateString.match(datePattern); if (!match) { return null; } var month = match[1].padStart(2, '0'); var day = match[2].padStart(2, '0'); var year = match[3]; if (year.length === 2) { year = '20' + year; } return month + '/' + day + '/' + year; }

unsupp0rted 2y ago
This is what I'm saying. It takes a dozen LoC to do what Excel is theoretically built to do: format a column of dates "as date".

deleted 2y ago

[deleted]

hot_gril 2y ago

Does DATEVALUE not solve this?

unsupp0rted 2y ago
Nope. DATEVALUE fumbles this badly. It's precisely for parsing dates but on a column with 1-digit vs. 2-digit days/months and 2-digit vs. 4-digit years (all in d/m/y format, mind you), it fails in one instance or the other.
- maxerickson 2y ago
  Text to columns with a date value? (it works fine if it's one columns) I expect it gets your example there right, but you may have other issues in mind that you didn't push into the example.
- elmer007 2y ago
  I just put all 6 of the examples above into Excel as text, then used DATEVALUE to parse them. It worked correctly for all 6. However, in the immediate parent comment, you mention d/m/y format, which 3 of the examples above could not be, so perhaps the details of the issue you've encountered are more subtle (such as 2-digit years that require context to determine the century); but, at present, it looks like DATEVALUE handles this well.
- m16ghost 2y ago
  > (all in d/m/y format, mind you) The examples you gave are in m/d/y format though, and DATEVALUE() parses your examples correctly into Jan 15th, 2024 and January 3rd, 2024. DATEVALUE() parses ambiguous short date formats (e.g. 1/3/24) using the short date format specified in the Region settings of Windows Control Panel. So if you want to parse d/m/y format, you can try changing the settings there.

SECProto 2y ago

If you type those into cells in excel, they all display the same. Excel doesn't store them as dates, it just displays them as such. If you format it as a generic number, the first three lines all show 45306, while the latter three lines show 45294 as the cell value.

unsupp0rted 2y ago
In my case, usually the column comes into Excel via copy/paste of a dozen mixed columns, or File-Open on a CSV. The workaround, at least for CSVs, is to do an import via "Get Data" -> "From Text (legacy)" and tag the relevant column as "date". This doesn't always work though.

aspenmayer 2y ago

I learned how to format cells in early 2000s or maybe even 90s on Excel, so I know this functionality has existed for a long time, and these instructions are valid all the way back to Excel 2016, and up to current Excel 365. On Excel desktop, you can even set your own custom date parsing format. I don't know if this will work for you, but it is built-in and supported using the GUI, no coding/scripting necessary. https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e https://support.microsoft.com/en-us/office/format-a-date-the...

zamadatix 2y ago

The main hangup is probably around what year "24" should auto convert to being ambiguous as that adds an extra IF() to handle converting to 2024 instead of 1924. Beyond that it should just be =TEXTSPLIT(A1, "/") and =DATE(D1, C1, B1). If I was going to drop my hackerman shades and pull up to an Excel code golf competition this is the "fewest total characters using a single cell" I could come up with: =REGEXREPLACE(A1 "^0?(\d{1,2})\/0?(\d{1,2})\/(..)?(\d{2})$", "20$3/$2/$1")

jazzyjackson 2y ago

yyyy-MM-dd HH:mm works best for me, stored in a text field so it doesn't get converted to that ungodly float-since-1899 format and rendered by reference via =TEXT but can still be sorted and compared alphabetically it is unforgivable that a program used for timeseries data can't interpret an ISO 8901 without third party macros