Skip to main content

The formula which finds the last working day of the year

  • December 11, 2022
  • 4 replies
  • 51 views
A
Forum|alt.badge.img+10

I was setting a "Happy Christmas" automation. I could have set the automation time trigger to "YEAR-12-31", but using the last working day as a trigger is better. Firstly I searched on the community for this kind of formula. I could not find. So, I wanted to share with you.

The weekday number of the 29th day is important, since if it is friday, this day should be trigger date, since 31th is sunday, etc. 

Also, if you have a better formula or find a problem in my formula, please share your comments.

 

IF(AND(WEEKDAY(YEAR(TODAY()) & "-12-29") = 5, YEAR(TODAY()) & "-12-29" = DATETIME_FORMAT(TODAY(),"YYYY-MM-DD")),1, IF(AND(WEEKDAY(YEAR(TODAY()) & "-12-29") = 4, YEAR(TODAY()) & "-12-30" = DATETIME_FORMAT(TODAY(),"YYYY-MM-DD")),1, IF(YEAR(TODAY()) & "-12-31" = TODAY(),1,0)))

 

 

 

      4 replies

      kuovonne
      Forum|alt.badge.img+29
      • kuovonne
      • Brainy
      • December 11, 2022

      Thanks for sharing your formula. Have you considered using a SWITCH() function instead of nested IF()?

        Ben_Young1
        Forum|alt.badge.img+22
        • Ben_Young1
        • Brainy
        • December 12, 2022

        This is an interesting formula!
        I ran into a similar requirement sometime last year and originally thought to use a formula. For the sake of adding to the historical reference of the forum, here's how I approached it:

        I quickly realized that if I wanted to account for a bunch of dynamic holidays (and just general dates of importance within that specific organization), I would have to commit to a formula over 850 lines long which was simply way too much to maintain and incurred too much tech debt.

        My solution was to create a table to contain all of the team's holidays and key dates that they wanted to reference for use in automated emails. Each record was a holiday on a unique date.
        So there were multiple Christmas records, but they were unique based on that given year's Christmas.

        On the Holidays table, an automation would run each morning and look at a formula field that returned a value if that holiday was on that particular day. If a record's formula field flagged it as being applicable for that given day, it would continue the automation.

        I created two methods for generating the emails:
        The initial method that I delivered was a long-text field that contained the desired templated email.
        The long-term method was creating a communications base for the wider company.
        The automation would then make an API call to that communications base and query for the correct email template it needed for that given day.

        The only thing that the workflow requires from users is that they need to make sure that all the company-specific holidays were provided in the Holidays table every few years.
        All US federal bank holidays are automatically uploaded via an annually triggered automation that makes an API request for the next year's federal holidays.

        Additionally, when interacting with customer records, the team would simply designate the desired automated email behavior for a given customer.
        They could desginate whether a customer should receive automated emails for every single federal holiday, Custom company designated days of importance, or nothing at all.

          A
          Forum|alt.badge.img+10

          This is an interesting formula!
          I ran into a similar requirement sometime last year and originally thought to use a formula. For the sake of adding to the historical reference of the forum, here's how I approached it:

          I quickly realized that if I wanted to account for a bunch of dynamic holidays (and just general dates of importance within that specific organization), I would have to commit to a formula over 850 lines long which was simply way too much to maintain and incurred too much tech debt.

          My solution was to create a table to contain all of the team's holidays and key dates that they wanted to reference for use in automated emails. Each record was a holiday on a unique date.
          So there were multiple Christmas records, but they were unique based on that given year's Christmas.

          On the Holidays table, an automation would run each morning and look at a formula field that returned a value if that holiday was on that particular day. If a record's formula field flagged it as being applicable for that given day, it would continue the automation.

          I created two methods for generating the emails:
          The initial method that I delivered was a long-text field that contained the desired templated email.
          The long-term method was creating a communications base for the wider company.
          The automation would then make an API call to that communications base and query for the correct email template it needed for that given day.

          The only thing that the workflow requires from users is that they need to make sure that all the company-specific holidays were provided in the Holidays table every few years.
          All US federal bank holidays are automatically uploaded via an annually triggered automation that makes an API request for the next year's federal holidays.

          Additionally, when interacting with customer records, the team would simply designate the desired automated email behavior for a given customer.
          They could desginate whether a customer should receive automated emails for every single federal holiday, Custom company designated days of importance, or nothing at all.


          Thank you for your comment. 

          We do not send emails for each holiday or special day for now, but if we would, your method is the best. Some rows in your table can be formulized. For example, if 29th of October is a national special day,  

          YEAR(TODAY()) & "-09-29"

          can be used. You do not need to enter these date each year, but I think the API request handles these kind of dates 😄 Thank you, again.

          A
          Forum|alt.badge.img+10

          Thanks for sharing your formula. Have you considered using a SWITCH() function instead of nested IF()?


          No, I didn't know it. Thank you. I will use it.

          Terms & ConditionsAccessibility statement