I just finished writting a formula that calculates the last weekday of the previous quarter using a formula step in the Prepare recipe for this thread. Here is the formula:
if(datePart(inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), 'dayofweek') < 6, inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), inc(inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), mod(datePart(inc(inc(asDate(concat(datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'year'), '-', datePart(inc(trunc(now(), 'days'), if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 0, 3, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 1, 1, if(mod(datePart(trunc(now(), 'days'), 'month'), 3) == 2, 2, 0))) * -1, 'months'), 'month'), '-1'), 'yyyy-MM-dd'), 1, 'months'), -1, 'days'), 'dayofweek'), 5) * -1
, 'days'))
Now it's clear that anyone looking at that formula other than the creator will struggle to understand what's doing and how. So this idea is to improve readability of the formula preprocesor by supporting code formatting, code blocks, commenting (which is a separate idea) and collapsing and expanding code blocks.
