Jun 27 2021 05:11 AM
Jun 27 2021 05:11 AM
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.
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
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.
Jun 27 2021 12:55 PM
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)));
Jun 28 2021 02:45 PM
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.
Jun 28 2021 03:50 PM
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)));