Skip to main content
Solved

Workay_diff with variable in holidays

  • July 30, 2020
  • 5 replies
  • 80 views
A

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 !

Best answer by Justin_Barrett

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 :

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} & "")

5 replies

Justin_Barrett
Forum|alt.badge.img+21

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.

A
  • Arthur_Couge
  • Author
  • New Participant
  • July 30, 2020

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 :

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 !

Justin_Barrett
Forum|alt.badge.img+21

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 :

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} & "")
ScottWorld
A
A
  • Arthur_Couge
  • Author
  • New Participant
  • July 30, 2020

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:

Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

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)

Terms & ConditionsAccessibility statement