Task reminder in SharePoint Online, but don't want to include the weekend days?

by MGLovesFlow on ‎07-09-2018 07:32 PM

Do you like the Network Days function in Excel? And do you need to create reminders to do a task but do not want to include weekend days - here is a trick to exclude them. You can re-create it with SharePoint Online and Flow in 6 simple steps.

 

  1. Create a SharePoint List, name it "Network Days"
  2. Create two SharePoint columns:
    1. Create calculated column, name it, "Alert Date"; add the calculation: =IF(WEEKDAY(Created)>3,Created+5,IF(WEEKDAY(Created)>1,Created+3,Created+4))
    2. Create person column, name it "Writer"; allow person only and make this field required.
  3. Create a Flow from blank and name it "When an item is created - send an alert 3 days after create date". Select Trigger "SharePoint - When an item is created": Choose Site and List "Network Days"; Select "Add an Action" and type "Schedule - Delay Until": Add dynamic content "Alert Date" in Timestamp field; Select "Add an Action" and type "Office 365 Outlook - Send an Email": Add dynamic content "Writer Email" in the "To" field and format the Subject and Body accordingly.
  4. Create a new item and confirm date is 3 days plus the create date not including the weekend. 
  5. Flow gets created and pauses until create date plus 3 working days.
  6. On Alert Date, you should receive the reminder email.

  1. Create a SharePoint List, name it "Network Days".  You can do this by Adding an App and creating a custom list in a SharePoint Online team site.

addlist.jpg2. Create two SharePoint columns:

    • Create calculated column, name it, "Alert Date"; add the calculation: =IF(WEEKDAY(Created)>3,Created+5,IF(WEEKDAY(Created)>1,Created+3,Created+4))
    • Create person column, name it "Writer"; allow person only and make this field required.alert-date.jpg

      columns.png

3.  Create a Flow from blank and name it "When an item is created - send an alert 3 days after create date". Select Trigger "SharePoint - When an item is created": Choose Site and List "Network Days"; Select "Add an Action" and type "Schedule - Delay Until": Add dynamic content "Alert Date" in Timestamp field; Select "Add an Action" and type "Office 365 Outlook - Send an Email": Add dynamic content "Writer Email" in the "To" field and format the Subject and Body accordinglflow.png 

4. Create a new item and confirm date is 3 days plus the create date not including the weekend.

 new-item.jpg

5. Flow gets created and pauses until create date plus 3 working days.

 

pause.jpg

 

6. On Alert Date, you should receive the reminder email.

 email.jpg

 

Resource:

Flow Action - Delay until: http://www.rjesh.com/2017/03/microsoft-flow-pause-till-date.html

SharePoint Weekday function: http://blog.pentalogic.net/2008/11/working-days-weekdays-holidays-sharepoint-calculated-columns/

Comments
by johnschorer
on ‎07-11-2018 01:50 PM

 

 

Good post - how about this slightly advanced version of this:

 

The networkdays function has an optional third parameter for "holidays". Let say that the holdays are in a SP list because even fixed holidays like New Years may be observed on a Monday or Friday if the actual holiday falls on the weekend.

Meet Our Blog Authors
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • I'm the owner of ThriveFast, an Office 365 consulting company. I'm a developer and have been working with SharePoint since 2007.
  • 7x Microsoft Business Solutions MVP (CRM)
  • Solution Architect with Slalom, and organizer of the Boston Office 365 User Group, and long term SharePoint/Office 365 veteren. Find more at http://www.davidlozzi.com. Follow @DavidLozzi
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( http://triad.co.uk)