Array / join / substitute

[Miroslav_Tunjic]

Hi All!

I am trying to build the formula to show working and non workind days of a month in one field

Rules:

• Field musst have 31 charachters, for 31 days of the Month
• “N” for non working days and “J” for days worked

Example:
Max worked on a project on 13th & 15th

Result should be:
NNNNNNNNNNNNJNJNNNNNNNNNNNNNNN

i have a lookup field showing the days worked, and i know this goes something like
Substitute( REPT(“N”,31), {Days worked}, “N”,“J”) but i am just stuck.

thx for any help!

[W_Vann_Hall]

You biggest problem will likely be the current inability to create a generalized looping process within Airtable that can then be applied to an array of records. Instead, I suspect you will have to create a formula that explicitly addresses each of 31 days — probably a big ugly nested IF() statement — with each day’s formula checking to see if that day should be flagged as ‘N’ or ‘Y’.

I thought I had put together a day-driven demo somewhere, but it must not have been ready for prime time, as I can’t find it anywhere. However, some of the faux bar charts in my Sales CRM Dashboard take a similar approach, and this reply to another post discusses the technique in more detail. The Leads Demo base discussed in this reply may also be of some interes.

[Elias_Gomez_Sai]

Anyway, what do you want to achieve? Maybe we could advise in other ways to do it.

[Miroslav_Tunjic]

Thank you so very much for wanting to help!!

it´s about generating a file for the workers health insurance.

one txt field musst contain 31 x “N” or “J”, N for non working days and “J” for worked days.

• have a table with events & dates linked to staff table
• have set up the rolup und lookfield to find calculate and display working hours and dates

now only thing left is finding out how to substitute the days with “J” , creating this “NNNNNNJJNNN…” field

i have this in excel worked out like this:
$E$1:$E$89 = working days
$C$1:$C$89=$C2 = lastname
$D$1:$D$89=$D2 = firstname

{=CONCAT(MID(IF(ISNUMBER(MATCH(ROW(INDIRECT(“1:31”));$E$1:$E$89*($C$1:$C$89=$C2)*($D$1:$D$89=$D2);0));REPT(“J”;31);REPT(“N”;31));ROW(INDIRECT(“1:31”));1))}

Thx!

[W_Vann_Hall]

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!

[Miroslav_Tunjic]

First of all,
@Elias_Gomez_Sainz
@W_Vann_Hall

THANK YOU!

Second, i think i found a very interesting problem, not unsolvable, just i dont see it :slightly_smiling_face:

I am building a simple Project / Staffing / Timetracking base. Easy!
Now, if i include a payroll to it, it gets interesting.

Here is a preview:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

Rules:

1. you must have time tracking for every one! per project! per day! per shift!
2. you must be able to export the data on a daily basis, (some guys work today -> csv export -> import somewhere else, finished)
3. you mujst have MONTHLY sumarry per person to do a payroll
4. this has to be in the specific form - (@W_Vann_Hall , Thank you!, i am getting there :slightly_smiling_face: )
5. per law: if someone workes over the midnight, it has to be calculated as two working days, EARNINGS PER DAY!

in a staffing table i have a “test NNJJ” view, where i can export data for daily reports, but how do i do the monthly payroll?

first thught was to have the months summed in the staff table so

• in a table “staff” i trield to rollout the months and working days, works fine, it rolls out all the monts and all the dates … but how do i filter it “per month”

do i need another table like : if in timetracking table, month = x, then sum working hours? and then do this for every month in every separate collumn?

• nother thing i dont understand is how to separate two days in the midnight, in a nighshift?

clocking out and in is not an option

sorry, my brain just stopped.

thx!

[W_Vann_Hall]

I’m walking out the door, but I’ll try to get back to you later.

One quick idea, either as monthly calculation, sanity check, budgetary use, whatever: You can quickly arrive at the number of days worked with

LEN(DaysWorkedEncoded)-LEN(SUBSTITUTE(DaysWorkedEncoded,'J','N'))

^---- Note: Suggested formula is incorrect. See next reply.

[W_Vann_Hall]

Well, I see I obviously never walked back in the door. Are you still wrestling with how to extract the information you need? If so, let me know, and I’ll see if I can help…

…and, one hopes, do a better job of helping than I did in my last message, which contained an embarrassingly incorrect formula snippet. The code you need to arrive at the number of days worked would actually be

LEN(DaysWorkedEncoded)-LEN(SUBSTITUTE(DaysWorkedEncoded,'J',''))

The one previously provided would always equate to zero (0).