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!
