Help

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

Re: Referencing Holiday String in a formula

Solved
Jump to Solution
1226 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Becca_Pickett
4 - Data Explorer
4 - Data Explorer

Hi! I’m working on setting up a long-running {Task Due} date formula field that will eventually span across multiple years. The {Task Due} date is already successfully calculating a variable workday amount prior to the {Key SR Date} (the date of the actual event) using negative values found in the {Days Out} field. I’ve created an IF set up to accommodate some of the {Days Out} are 0, where I want the output to be the same as the input. Here’s the formula I’ve got going successfully:

IF(({Days Out}=0), {Key SR Date}, (WORKDAY({Key SR Date}, {Days Out})))

Now, I want to factor US holidays (and specifically a string I made for my company’s holiday list). I created a separate table with a {Reference Year} and a {Holiday String} field so that I can create a string for 2022, 2023, etc. I’ve used lookup fields on the current table so that the correct Reference Year is applied to each record, and it displays the applicable {Holiday String} from the other table. My thought was, shouldn’t I be able to use the content of that Holiday String Lookup Field as an input in my formula for the holiday string info in the WORKDAY function? It returns an error, but only for the cells where the {Days Out} value is not 0 (ie the ones where a calculation needed to be made). Here is the formula I tried to use to make that happen:

IF(({Days Out}=0), {Key SR Date}, (WORKDAY({Key SR Date}, {Days Out}, {Holiday String})))

Some context on the field types:
{Days Out} - Integer, using negative numbers
{Key SR Date} - Formula finding the latest of six Date fields (GMT turned on for all fields), none of the fields have times listed
{Holiday String} a Lookup field for another table with the following content:
‘2022-01-17, 2022-02-21, 2022-02-21, 2022-04-15, 2022-05-30, 2022-06-20, 2022-07-04, 2022-09-05, 2022-11-24, 2022-12-26’

Thanks for the help! I’ve been digging around in the community and it’s been hugely helpful in setting up my base!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Lookup fields are often returned as arrays whereas your formula needs to be fed a string. The common workaround for this is to do the following, which forces a string:

{Lookup Field}&""

In your case, that would mean replacing {Holiday String} with {Holiday String}&""

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

Lookup fields are often returned as arrays whereas your formula needs to be fed a string. The common workaround for this is to do the following, which forces a string:

{Lookup Field}&""

In your case, that would mean replacing {Holiday String} with {Holiday String}&""

Thank you! That was the solution I needed!