Help

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

Exclude public holidays from counting date - date

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