Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Exclude public holidays from counting date - date

Topic Labels: Formulas
Solved
Jump to Solution
2225 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Theo_Djerkallis
4 - Data Explorer
4 - Data Explorer

Heyy everyone,

I wonder if you can help, I am not the best at Airtable so rely mostly on learning as I go and your threads.

I currently have several columns which count the time(days) between Date 1 and Date 2 but now I am looking to make it exclude a list of public holidays, currently, it just measures working days.

So this is the formula currently -

IF(OR({DATE 1} = BLANK(),{Date 1}<{Date 2}), BLANK(), MAX(WORKDAY_DIFF({Date 2},{Confirmed Date 1})-1,0))

But I can not seem to figure out how to make this formula not count the below, I’d have to implement this into several columns with similar formulas !

01/01/2021
02/04/2021
05/05/2021
31/05/2021
30/08/2021
27/12/2021
28/12/2021
03/01/2022
15/04/2022
18/04/2022
02/05/2022
02/06/2022
03/06/2022
29/08/2022
19/09/2022
26/12/2022
27/12/2022
02/01/2023
07/04/2023
10/04/2023
01/05/2023
29/05/2023
28/08/2023
25/12/2023
26/12/2023

Any help is welcomed :slightly_smiling_face:

Thanks

1 Solution

Accepted Solutions
Databaser
12 - Earth
12 - Earth

Hi @Theo_Djerkallis and welcome to the community!

You can incorporate WORKDAY_DIFF()

in your formula, which gives you the possibility to exclude holidays. Do mind it will also exclude weekends.

image

Also see the formula field reference.

See Solution in Thread

2 Replies 2
Databaser
12 - Earth
12 - Earth

Hi @Theo_Djerkallis and welcome to the community!

You can incorporate WORKDAY_DIFF()

in your formula, which gives you the possibility to exclude holidays. Do mind it will also exclude weekends.

image

Also see the formula field reference.

Ah, my bad, I see you’re already using WORKDAY_DIFF(). Have you tried adding your holidays into the formula like:

WORKDAY_DIFF({Date 2},{Confirmed Date 1}, 2021-01-01, 2021-04-02, …)?

You can create a hidden field where you put in all the holidays, so it is a little bit less static.

image

with “Calculation” = WORKDAY_DIFF({date 1}, {date 2}, holidays)