Hacker News Reader

Comment by unsupp0rted | Hacker News Reader

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.

View on HN

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;
}

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".

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.

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.

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...

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

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

Does DATEVALUE not solve this?

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.

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.

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.

> (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.

Reply on news.ycombinator.com