LAMBDAs do not need to be complicated to be useful - discussion

[Peter Bartholomew]

I recently came across a novel representation of a date which uses the 4-digit year number followed by 3-digits in the range 1-365 representing the day.  The requirement was to convert the string into a standard Excel datevalue.

Need help with Date stored as year followed by day number - 2021001, 2021002, 2021221 etc | Chandoo....

My starting point was to write trivial functions to extract the day and year as numbers.

DAYλ
= LAMBDA(dt, MOD(--dt,1000))

YEARλ
= LAMBDA(dt, QUOTIENT(--dt,1000))

The datevalue is then returned  by

= DATEVALUEλ(dates)

where the new Lambda function is defined by

DATEVALUEλ
= LAMBDA(dt,
    LET(
        d,  DAYλ(dt),
        yr, YEARλ(dt),
        DATE(yr,1,d)
    )
  )

This is not very different from standard Excel in substance, but the presentation is radically different to the point where some users may fail even to recognise the solution as Excel.

Whether that is good or bad is very much a matter of opinion and I suspect radically different opinions may be held by different elements of the community.

[#2]

@Peter Bartholomew 

Honestly I don't see which pros we have separating on 3 different functions when each used only once; adding LET inside and using kind of Hungarian notation. If function is needed (lambda, not VBA), I'd simplify to

number2Date = lambda(n, DATE(n/1000, 1, MOD(n, 1000)));

[#3]

@Sergei Baklan 

I agree that, for a one-off conversion, the single formula is probably better.  If, on the other hand, the extraction of day and year are required for other formulas in the workbook, then why not use them as part of this conversion to standard Excel DateValues?

The idea was to demonstrate how the detailed mathematical manipulation could be encapsulated within meaningful functions, and that it is possible to create a hierarchy of lambda functions from simple to more complex that serve the needs of a specific workbook or family of workbooks.

[#4]

@Peter Bartholomew 

Peter, I have noting against the idea, but evil is in details. Depends on concrete sample and target auditory. I believe if to show your sample to unexperienced in DA Excel people, they say "Very interesting, I'll try sometime" and majority will never return to that. Since they see that for simple transformation they need to create 3 separate functions, use name convention to be different from built-in function, plus nested LET.

That's my only worry, have nothing against the approach.

To complicate even more such simple transformation

datePart    = lambda(n, f, f(n) );
nYear       = lambda(n, datePart(n, lambda(n, n/1000)));
nDay        = lambda(n, datePart(n, lambda(n, MOD(n,1000)) ));
nDate       = lambda(n, DATE(nYear(n), 1, nDay(n)));