cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bgoldberger
Helper I
Helper I

Dayofyear expression to exclude holidays

Hello all,

 

I have a flow that is triggered by an email and results in a task being created in microsoft planner.  The 'startdate' for the task is the same day that task is created (utc(Now)) and the 'duedate' is set for 2 days from the startdate.  I pulled a formula elsewhere on the web that has successfully excluded weekends from the duedate calculation using a couple of initialize variable steps.  I am now searching for a formula to use that will exclude holidays.  My company observes 11 holidays in the US.  I was wondering if I might be able to use the dayofyear expression to exclude those 11 holidays?

 

Here are some snips of the flow:

 

bgoldberger_0-1609273650812.pngbgoldberger_1-1609273719288.png

 

Thank you all for your help!

 

Brett

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @bgoldberger :

Do you want to get the date two days after the current date (and exclude weekends and weekdays).

Firstly,I am afraid that dayofyears cannot meet your requirements, because it is impossible to 100% determine the day of the year for a certain holiday.

Secondly,please try this flow:

1.JPG

 2.JPG3.JPG4.JPG

Refrenence Expression:
1\

[
  {
    "Date": "2021-1-1"
  },
  {
    "Date": "2021-1-18"
  },
  {
    "Date": "2021-2-15"
  },
  {
    "Date": "2021-5-31"
  }
]

/*Need to list all holidays*/

2\

addDays(utcNow(),variables('adddays'))
3\
addSeconds(item()?['Date'],1)
4\
addDays(utcNow(),variables('adddays'))
5\
addSeconds(item()?['Date'],86399)
6\
dayOfWeek(addDays(utcNow(),variables('adddays')))
7\
dayOfWeek(addDays(utcNow(),variables('adddays')))
8\
addDays(utcNow(),sub(variables('adddays'),1))
The Result:
5.JPG
Because I was doing the test on December 30, there was a New Year’s Day and a weekend in between, so the final result was January 4
Best Regards,
Bof

View solution in original post

4 REPLIES 4
v-bofeng-msft
Community Support
Community Support

Hi @bgoldberger :

Do you want to get the date two days after the current date (and exclude weekends and weekdays).

Firstly,I am afraid that dayofyears cannot meet your requirements, because it is impossible to 100% determine the day of the year for a certain holiday.

Secondly,please try this flow:

1.JPG

 2.JPG3.JPG4.JPG

Refrenence Expression:
1\

[
  {
    "Date": "2021-1-1"
  },
  {
    "Date": "2021-1-18"
  },
  {
    "Date": "2021-2-15"
  },
  {
    "Date": "2021-5-31"
  }
]

/*Need to list all holidays*/

2\

addDays(utcNow(),variables('adddays'))
3\
addSeconds(item()?['Date'],1)
4\
addDays(utcNow(),variables('adddays'))
5\
addSeconds(item()?['Date'],86399)
6\
dayOfWeek(addDays(utcNow(),variables('adddays')))
7\
dayOfWeek(addDays(utcNow(),variables('adddays')))
8\
addDays(utcNow(),sub(variables('adddays'),1))
The Result:
5.JPG
Because I was doing the test on December 30, there was a New Year’s Day and a weekend in between, so the final result was January 4
Best Regards,
Bof

View solution in original post

Hi @v-bofeng-msft ,

 

Wow, this is fantastic and thanks so much for your help.  I can tell this will function just as I was hoping for.  The output from the compose function should work for my due date in planner.  But for whatever reason, the flow keeps timing out at the do until step.  I've tried throwing in a couple of delays to see if that will work but it isn't getting pushed through.  Maybe this is just temporary?  Let me know if you might have any suggestions.  Thank you!

 

bgoldberger_0-1609362932220.png

Flow triggered by email:

 

bgoldberger_1-1609363194236.png

bgoldberger_2-1609363217434.png

 

bgoldberger_3-1609363281209.png

bgoldberger_4-1609363309530.pngbgoldberger_5-1609363337881.png

bgoldberger_6-1609363361621.png

bgoldberger_7-1609363391814.pngbgoldberger_8-1609363408112.pngbgoldberger_9-1609363421949.png

 

v-bofeng-msft
Community Support
Community Support

Hi @bgoldberger :

There is a problem with the 'condition' action:

I have marked the error:

1.JPG

The correct setting is

2.JPG

Best Regards,

Bof

 

Hi @v-bofeng-msft ,

 

That was certainly it!  I've been working on this the past week and this really helps.  Thanks again and happy new year.

 

Brett

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (1,114)