Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Workay_diff with variable in holidays

Topic Labels: Formulas
Solved
Jump to Solution
757 5
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello !

I made a formula to get all the holidays for the current year automatically and I can’t use that value directly with WORKDAY_DIFF in another formula. I have to copy and paste.

If anyone has the answer I would love to have it, thanks a lot !

1 Solution

Accepted Solutions

Thanks for the added detail. It definitely helps to know that you’re using a lookup to pull that into another table. Lookup fields, even if they only pull from a single record, most often return arrays. Because of this, referring to a lookup field in a formula will bring in the array. In your case, where you need to insert this looked-up date string into WORKDAY_DIFF(), you’ll probably need to convert that array—even though it only contains a single item—back into a string by concatenating it with an empty string:

WORKDAY_DIFF({Start Date Field}, {End Date Field}, {Lookup Field} & "")

See Solution in Thread

5 Replies 5

Welcome to the community, @Arthur_Couge! :grinning_face_with_big_eyes: What formula are you using to get the list of holidays, and what does it output? There may be a way to achieve what you want, but we’ll need more details.

Hello Justin, thank you veryy much ! :grinning_face_with_smiling_eyes:

I use two formula, one to get the holidays I want to exclude and another to get them into string, then I use a lookup on another table and I try to get the data from there.

Some screen will be more explicit :
image
Formulas :

  • Holidays : YE&’-01-01, ‘&DATETIME_FORMAT({Lundi de paques}, ‘YYYY-MM-DD’)&’, ‘&YE&’-05-01, ‘&YE&’-05-08, ‘&DATETIME_FORMAT({Ascension}, ‘YYYY-MM-DD’)&’, ‘&DATETIME_FORMAT({Lundi de pentecôte}, ‘YYYY-MM-DD’)&’, ‘&YE&’-07-14, ‘&YE&’-08-15, ‘&YE&’-11-01, ‘&YE&’-11-11, ‘&YE&’-12-25’

YE being the current year

  • Holidays String : ‘’’&ARRAYJOIN(ARRAYCOMPACT(Holidays),’, ‘)&’’’

Nota : I copied a lot from this work : U.K. Holidays for WORKDAY() and WORKDAY_DIFF(). (Bonus: Easter!)

Thanks again !

Thanks for the added detail. It definitely helps to know that you’re using a lookup to pull that into another table. Lookup fields, even if they only pull from a single record, most often return arrays. Because of this, referring to a lookup field in a formula will bring in the array. In your case, where you need to insert this looked-up date string into WORKDAY_DIFF(), you’ll probably need to convert that array—even though it only contains a single item—back into a string by concatenating it with an empty string:

WORKDAY_DIFF({Start Date Field}, {End Date Field}, {Lookup Field} & "")

Well… It actually work !

Thank you I didn’t expected it to be an array since there is single quote on the field binded in the lookup, good to know !

Thanks again, have a great day :slightly_smiling_face:

Yeah, it’s tough to determine what it is sometimes. I’m working (very slowly) on creating a reference to make it easier to know what types to expect from a lookup field in different situations (field types, single vs multiple links, etc)