Okay, now that we’ve got the answer, here is the solution I have cobbled together.
Start Date(calender)
End Date(calender)
Holiday Duration(formula)
IF(AND({Start Date},{End Date}),WORKDAY_DIFF({Start Date},{End Date}))
Holiday String
left until last, or this info will be lost
Trimmed End
REGEX_REPLACE({Holiday String}&"","\\,\\s$", "")
Because the formula returns a , stuck on the end of the last item
Airtable keeps adding escape \ here, so if you get an error check it hasn’t added more.
Workday Calculation(formula) to test
WORKDAY({Start Date}, {Holiday Duration}, {Trimmed End}&"")
and here is the obscene formula. Well that was the intention but its too big to post here
Body is limited to 32000 characters; you entered 196483.
So I’ve opened up the first block to show how its formatted, and the others follow the same format and duplicate it 365 times for every day of the year.
Apologies for not giving credit but cannot find where I found it, I think its on this forum, and was for a different problem, so it has been reformatted to provide ISO dates. If you recognise it, thanks very much.
=====
IF(
AND(
DATETIME_PARSE('01-01-'&
IF(YEAR({Start Date})=YEAR({End Date}),
YEAR({Start Date}),
IF('01-01'<
DATETIME_FORMAT({Start Date},'MM-DD'),
YEAR({End Date}),
YEAR({Start Date})
)
)
)
>={Start Date},
DATETIME_PARSE('01-01-'&
IF(YEAR({Start Date})=YEAR({End Date}),
YEAR({Start Date}),
IF('01-01'<DATETIME_FORMAT({Start Date},'MM-DD'),
YEAR({End Date}),
YEAR({Start Date})
)
)
)
<={End Date}
),
IF(YEAR({Start Date})=YEAR({End Date}),
YEAR({Start Date}),
IF('01-01'<DATETIME_FORMAT({Start Date},'MM-DD'),
YEAR({End Date}),
YEAR({Start Date})
)
)&'-01-01'&', '
)&
=====
