Help

Re: Holiday String for Workday function

1888 0
cancel
Showing results for 
Search instead for 
Did you mean: 
David_MacDouga1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone,

I have a Start Date(calendar) and End Date(calendar)

I want to be able to generate the required ISO date string for all the dates inclusive of the Start and End Dates, such that it can be inserted into the [HOLIDAYS] option of the WORKDAY function.

Initially I thought this would be as simple as finding a formula function, something like DateBetween() or DateRange() but alas, all I can find is some seriously huge formulas (1000’s of lines long) which don’t quite work and are impossible to edit without errors.

Let’s say my Start Date is 25 July 2022 and End Date is 27 July 2022.

What is the simplest way to get a string like this ‘2022-07-25, 2022-07-26, 2022-07-27’

Apologies if this has been asked before, but I couldn’t find the answer so happy to be directed to an already answered question if it exists.

Thanks

7 Replies 7
Russell_Findlay
8 - Airtable Astronomer
8 - Airtable Astronomer

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

=====

David_MacDouga1
5 - Automation Enthusiast
5 - Automation Enthusiast

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!