Merging date fields

[Mar_Castro]

Hi everyone!

I have two lookup fields, each one of them with a list of dates, and I need to merge them in the same field, keeping the date format. I've been testing with some formulas like concatenate, arrayjoin, dateformat... but I haven't been able to solve this problem. Do you know how to do this? 

In case more detail is needed, I have a table with employees, another with Time Off Request and a third one with Holiday Calendars. Each line in the Time Off Request tab is linked to one employee who has requested that day off. Each date in the Holiday tab is assigned to several employees who are going to be off that day. Then, in the employee table, I have a lookup field for the dates from the Holiday Calendar tab, and another lookup field for the dates from the Time Off Request tab. I need to have all those dates in the same field and in a date format to be able to use it in a Timeline view. Is this possible?

Thanks for your support!!

Best regards,

Mar

[Stephen_Orr1 · Accepted solution]

Hi @Mar_Castro,

I would use a date scaffold table (ie, a prefilled table with 1 record per calendar day) for your time off table and drop the holidays table. Allow multiple employees to link to the same day record.

For holidays, add a field that lets you flag a record as a holiday (either text based or a checkbox) and filter out holidays as needed. Now you can easily set the condition on your lookup field in the Employees table to consider or ignore holiday records based on this flag.

This simplifies your base and if you ever use a timeline view for resource capacity planning via the utilization function, you can set holidays in the settings or point to a view of your time of table that excludes holidays.

Hope that helps!

-Stephen

[Mar_Castro]

Thanks, @Stephen_Orr1 
I don't think I can use the scaffold table, as I need to bring Holiday and Time off request info from an external tool. Still, I can use automation to create a record in the Time Off Request table for each Holiday record, and use a flag field to differentiate them and filter in the lookup field. 

And yes, I plan on using the utilization function, so your solution will be super helpful. Thanks again!