I’m so sorry – I could have sworn I posted this before the holidays. One morning in the shower I had this great idea of using binary encoding to create a 31-bit number that would represent a month of either work or non-work. Assuming `x000 0000 0000 0000 0000 0000 0000 0000`

represented a month in which no days were worked, all one would have to do would be to encode each day worked as `2^(Day-1)`

, roll up the values with a `SUM()`

aggregation function, and then, on the other end, encode the decimal value in binary, using `'N'`

for 0 and `'J'`

for 1. **Much** more elegant than my original suggestion.

The encoding went like a dream — and then I spent an hour trying to figure out how to generate the binary without the ability to loop. In the end, I fell back to the old stand-by, the ugly nested `IF()`

statement — at which point it made little sense to retain the binary encoding…

Here is a quick demonstration I tossed together. (Hmmm… maybe *that’s* why I didn’t post this — it doesn’t appear I actually finished the base.) In any case, as you add days worked to an individual’s record, you’ll see the extracted day+’,’ string added and the corresponding ‘N’ change to a ‘J’. Each month, you’ll have to update the `[Days Worked]`

table to reflect the new month, and you’re going to want to build in some error checking to guard against someone being paid for working on Frbruary 31. (I also see I left the powers-of-two field, `{PoT}`

, in there for no good reason…)

Actually, now that I look at it again,m I see this is **not** an ugly nested `IF()`

statement: It is actually an ugly concatenation of `IF()`

statements. The actual muscle behind the routine starts out something like this:

`IF(FIND('01,',DayIndex)=0,'N','J')&`

`IF(FIND('02,',DayIndex)=0,'N','J')&`

`IF(FIND('03,',DayIndex)=0,'N','J')&`

and continues in such a a manner until it reaches

`IF(FIND('29,',DayIndex)=0,'N','J')&`

`IF(FIND('30,',DayIndex)=0,'N','J')&`

`IF(FIND('31,',DayIndex)=0,'N','J')`

(`{DayIndex}`

is a roill-up of the individual day-or-the-month values with an aggregation function of `ARRAYJOIN(values)&','`

. The trailing `&','`

in the function — which **is** valid — is to guarantee a match on the last value in the rolled-up array.) (Actually, I just modified it, because it bugged me to see a single comma as the result when no days worked had been entered. The new aggregation function is

`IF(COUNTA(DaysWorked)>0,ARRAYJOIN(values)&',',BLANK())`

which, amazingly enough, also appears to be valid.)

Again, not a very pretty answer, but one that works!