Help

Workday_Diff and holidays in 3 different countries

Topic Labels: Formulas
Solved
Jump to Solution
748 1
cancel
Showing results for 
Search instead for 
Did you mean: 
L_C_Brown
4 - Data Explorer
4 - Data Explorer

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:

  1. 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.

  2. 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?

  3. 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!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hi L_C, I’ve put something together here that I think does what you’re looking for

Employees:
Screenshot 2022-07-06 at 1.42.21 PM

Holidays Grouped:
Screenshot 2022-07-06 at 1.42.26 PM

Holidays:
Screenshot 2022-07-06 at 1.42.47 PM

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

See Solution in Thread

1 Reply 1
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hi L_C, I’ve put something together here that I think does what you’re looking for

Employees:
Screenshot 2022-07-06 at 1.42.21 PM

Holidays Grouped:
Screenshot 2022-07-06 at 1.42.26 PM

Holidays:
Screenshot 2022-07-06 at 1.42.47 PM

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