News for hackers. Stuff that matters.
Re:The Microsoft Excel superstars throw down in Vegas by unsupp0rted on Friday June 14, 2024 @03:00PM (#40683844)
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.

Re:The Microsoft Excel superstars throw down in Vegas by HaZeust on Friday June 14, 2024 @03:07PM (#40683920)
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;
}
Re:The Microsoft Excel superstars throw down in Vegas by unsupp0rted on Friday June 14, 2024 @03:10PM (#40683958)
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".
Re:The Microsoft Excel superstars throw down in Vegas by SECProto on Friday June 14, 2024 @03:52PM (#40684390)
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.
Re:The Microsoft Excel superstars throw down in Vegas by unsupp0rted on Friday June 14, 2024 @04:29PM (#40684790)
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.

Re:The Microsoft Excel superstars throw down in Vegas by hot_gril on Friday June 14, 2024 @03:11PM (#40683965)
Does DATEVALUE not solve this?
Re:The Microsoft Excel superstars throw down in Vegas by unsupp0rted on Friday June 14, 2024 @03:15PM (#40684012)
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.

Re:The Microsoft Excel superstars throw down in Vegas by maxerickson on Friday June 14, 2024 @04:22PM (#40684707)
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.

Re:The Microsoft Excel superstars throw down in Vegas by elmer007 on Friday June 14, 2024 @05:06PM (#40685128)
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.
Re:The Microsoft Excel superstars throw down in Vegas by m16ghost on Friday June 14, 2024 @05:08PM (#40685151)
> (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.

Re:The Microsoft Excel superstars throw down in Vegas by aspenmayer on Friday June 14, 2024 @05:13PM (#40685203)
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...

Re:The Microsoft Excel superstars throw down in Vegas by zamadatix on Friday June 14, 2024 @05:20PM (#40685253)
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")

Re:The Microsoft Excel superstars throw down in Vegas by jazzyjackson on Saturday June 15, 2024 @01:35AM (#40687733)
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