Error using WORKDAY in formula with holiday dates to exclude

Question

I'm puzzled by an error I am coming up against ...

We pay our service providers' invoices (received by Monday) ten working days later (so, typically two weeks later). Using this formula, I get the result I want for a date of Monday 20/2/2023 being Monday 6/3/2023:

WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023')

When I add the next public holiday date into the formula, I get an ERROR:

WORKDAY({Send your invoice by}, 10, '01-01-2023, 02-01-2023, 07-04-2023, 10-04-2023, 01-05-2023, 08-05-2023, 29-05-2023')

I don't understand why adding that date breaks the formula, can anyone advise please?

augmented · Accepted Answer

Hi Hairtable. The WORKDAY function asks that your string of holiday dates are ISO formatted. AFAIK, that means YYYY-MM-DD. So, I'm not sure why your original date string is allowed, but I could imagine Airtable allows the MM-DD-YYYY format as well. Your original set can be mistaken for that format even though I'm sure you have it as DD-MM-YYYY. Then, you add 29-05-2023 and it breaks.

That’s really helpful. Huge thanks @augmented

I have reformatted the dates as you suggest, and the error is gone 🙂

Hairtable11 · Answer

Hi Hairtable. The WORKDAY function asks that your string of holiday dates are ISO formatted. AFAIK, that means YYYY-MM-DD. So, I'm not sure why your original date string is allowed, but I could imagine Airtable allows the MM-DD-YYYY format as well. Your original set can be mistaken for that format even though I'm sure you have it as DD-MM-YYYY. Then, you add 29-05-2023 and it breaks.

That’s really helpful. Huge thanks @augmented

I have reformatted the dates as you suggest, and the error is gone 🙂

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded