Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Error using WORKDAY in formula with holiday dates to exclude

Topic Labels: Formulas
Solved
Jump to Solution
1775 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Hairtable
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions
augmented
10 - Mercury
10 - Mercury

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. 

See Solution in Thread

2 Replies 2
augmented
10 - Mercury
10 - Mercury

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. 

Hairtable
6 - Interface Innovator
6 - Interface Innovator

That’s really helpful. Huge thanks @augmented 

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