Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 19, 2022 04:22 AM
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
Solved! Go to Solution.
Oct 19, 2022 05:08 AM
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.
Also see the formula field reference.
Oct 19, 2022 05:08 AM
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.
Also see the formula field reference.
Oct 19, 2022 05:30 AM
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.
with “Calculation” = WORKDAY_DIFF({date 1}, {date 2}, holidays)