Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Error using WORKDAY in formula with holiday dates to exclude

Topic Labels: Formulas
Solved
Jump to Solution
1386 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. 

That’s really helpful. Huge thanks @augmented 

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