Holiday String for Workday function

Hi

For simplicity

3 formulae field

1. dateformat (date1 - see Link for dateformat

2. dateformat(date 2 as above)

3. concatenate (formula 1, “, “ formula2)

Hope this helps

Thanks, but that will only give me the end dates, not all the dates in between.

Ah yes - apologies I read this too quickly

This thread may help if you have a small number of days …

Yes, I saw that example but it is limited to how many columns you add beforehand, so not very useful to give someone else to use who will just go about adding dates.

I have found another example, cannot remember where now, and I’ve modified the formula and it does work. I’ve hesitated to post it here as a solution because its about 400 lines long. Surely this is not the best solution, can’t believe airtable has a formula for Workday that takes an ISO date string, but offers no built-in way to create that string from a range.

If anyone is interested in the formula let me know, happy to post, just didn’t want to kill the page.

Yeah, sadly, Airtable has absolutely no looping/range mechanisms built into the product, neither in formulas nor automations. You can only achieve those sorts of results with JavaScripting or an automation tool that allows looping like Make.com. (Or perhaps with a gigantic formula like the one that you found. Lol.)

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'&', '
  )&

=====

I don’t understand how this forum is supposed to work. I posted a perfectly reasonable question, it got tagged as spam for some bizarre reason, then I posted a solution, and none of that is now showing here in this thread. It’s like they don’t want you to have it.

I now have this working, using formulas, combining peoples holidays, their countries public holidays and ending in a task end date that calculates based on workday formula. The only thing that doesn’t work is showing you the solution here. Sorry, I don’t get it!