Jul 05, 2022 09:20 AM
This formula worked fine, until we brought on an additional team member in another country. I am now tracking this data for 3 team members in the US, UK, and Germany. I think I ultimately have 3 issues I’m trying to resolve:
I’ve seen the great U.S. and U.K. Holidays tables created by user W_Vann_Hall, but my company has a number of additional days off outside of federal holidays I’d like to include. I’m going to go ahead and assume there is no easy way to do this.
I don’t see one for Germany, and I’m not up for building that myself. But would it be possible to alternatively upload .ics files with the holidays for each company into a single table (in ISO-Format), and link those records for each team member?
Assuming #1 above is more advanced than I’m capable of at this time, and #2 isn’t possible, is there a way to double nest the IF statements in my current formula to include 3 sets of holidays?
Here is the formula:
IF({Date Contract / Redlines / Feedback Returned to Stakeholder} = BLANK(), BLANK(), WORKDAY_DIFF({Date Received},{Date Contract / Redlines / Feedback Returned to Stakeholder},IF(Assignee="Joel Kiesey",'2021-07-05, 2021-09-06, 2021-09-20, 2021-10-25, 2021-11-25, 2021-11-26, 2021-12-24, 2021-12-27, 2021-12-28, 2021-12-29, 2021-12-30, 2021-12-31, 2022-01-17, 2022-02-25, 2022-03-08, 2022-04-08, 2022-04-15, 2022-04-18, 2022-05-26, 2022-05-30, 2022-06-06, 2022-07-04, 2022-08-12, 2022-09-05, 2022-10-03, 2022-10-21, 2022-11-24, 2022-11-24, 2022-12-23, 2022-12-26, 2022-12-27, 2022-12-28, 2022-12-29, 2022-12-30, 2023-01-02','2021-07-05, 2021-09-06, 2021-09-20, 2021-10-25, 2021-11-25, 2021-11-26, 2021-12-24, 2021-12-27, 2021-12-28, 2021-12-29, 2021-12-30, 2021-12-31, 2022-01-17, 2022-02-25, 2022-04-08,2022-05-30, 2022-06-20, 2022-07-04, 2022-08-12, 2022-09-05, 2022-10-21, 2022-11-24, 2022-11-24, 2022-12-23, 2022-12-26, 2022-12-27, 2022-12-28, 2022-12-29, 2022-12-30, 2023-01-02'))-1)
Thanks in advance for any advice/help!
Solved! Go to Solution.
Jul 05, 2022 10:48 PM
Hi L_C, I’ve put something together here that I think does what you’re looking for
Employees:
Holidays Grouped:
Holidays:
And so we’ll just need to populate the Holidays
table with all the holidays and link them to the respective Holiday Group
It’s basically a modified version of point 2 that you made
===
re: Point 1
Yeah…probably pretty difficult. Pretty useful as a data generator for you to populate the Holidays table though; we could potentially just paste the correctly formatted comma separated values into the Holidays
linked field in the Holidays Grouped
table and it would just be dealt with
re: Point 3
Yeah, doable, but also that’s a terrifyingly long formula and would get even longer if we were to do this. We can look into this, but I would much prefer we used the solution provided above as it seems cleaner
Jul 05, 2022 10:48 PM
Hi L_C, I’ve put something together here that I think does what you’re looking for
Employees:
Holidays Grouped:
Holidays:
And so we’ll just need to populate the Holidays
table with all the holidays and link them to the respective Holiday Group
It’s basically a modified version of point 2 that you made
===
re: Point 1
Yeah…probably pretty difficult. Pretty useful as a data generator for you to populate the Holidays table though; we could potentially just paste the correctly formatted comma separated values into the Holidays
linked field in the Holidays Grouped
table and it would just be dealt with
re: Point 3
Yeah, doable, but also that’s a terrifyingly long formula and would get even longer if we were to do this. We can look into this, but I would much prefer we used the solution provided above as it seems cleaner