Another representative line, and this one comes from an Excel spreadsheet. But, per Remy's Law of Requirements gathering ("No matter what the requirements doc says, what your users wanted was Excel"), this one was actually written by a developer. A developer who didn't understand how Excel works, but more important, didn't understand how dates worked either.

This comes from Ulysse J.

=CONCATENER(SI(MOIS($A18)>9;ANNEE($A18)-2000;(ANNEE($A18)-2000)*10);SI(JOUR($A18)>9;MOIS($A18);MOIS($A18)*10);JOUR($A18))

Now, the first thing: Excel function names are locale specific. This was written in France, so the functions are French. CONCATENER is "concatenate", SI is "if", MOIS is "month", and so on.

The purpose of this function is to convert a field (cell A18) in DD/MM/YYYY into YYMMDD. So how does it do this?

Well, we check the month. If it's greater than 9, we output the year minus 2000. If it's less than 9, then, we output the year minus 2000, multiplied by 10. That is to say, August, 2026 would start by outputting 260. We repeat this logic for the days: if the day is larger than 9, we output the month, otherwise we output the month times 10. Finally, we output the day.

This is attempting to do padding. There's just a problem. Imagine February 1st, 2009- an actual date in the document. We convert the year into 90, the month into 20, rendering the date as 90210. That's incorrect. And once we get to 2100, if there is still an Excel in 2100 (I joke: of course Excel will still exist in 2100. Humanity won't, but the robots will use Excel), this will also break. Not that it matters- I mean, YYMMDD doesn't make sense by that point.

Obviously, the correct solution is to use Excel's rich, built-in formatting functions to convert between date formats. It's easy! But Ulysse raises another point:

Extra points: even if you do not know how to do proper [formatting], the input format is guaranteed to have correct padding. I would just concatenate parts of it (treating dates as text is bad, but still less bad than treating them as integer triplets).

I will say this: I know a software developer wrote this, because your average Excel user could easily write bad formulas, but never bad in this kind of convoluted way. You need a real expert to do something this bad.

[Advertisement] ProGet’s got you covered with security and access controls on your NuGet feeds. Learn more.