Skip to main content

Notifications

Announcements

Upwork and Microsoft Power Platform Partnership
Welcome to the Power Platform Communities
Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Building Flows / Calculating dates in a...
Power Automate - Building Flows
Suggested answer

Calculating dates in a sharepoint list workflow

(0) ShareShare
ReportReport
Posted on by LC-16101139-0

I am currently working on an automation project at my institution. The plan is to move their Excel based trackers to SharePoint Lists since lists is more compatible with power automate. I am hoping to automate emails that are sent out as reminders based on dates in the trackers. The reminder emails will contain information that will be pulled from various columns in the list and sent off 3 days before a deadline. This workflow for automated email reminders is therefore massively dependent on dates in the list.

Typically in Excel, we use formulas to calculate these deadline dates and hold them in a column in the tracker for each task. Based on these dates, staff type out emails to send as and when things are due by manually checking the Excel trackers. The automation I am planning on doing will resolve that but there is issues with calculating these key dates in SharePoint Lists because you can't use formulas to the same extent as excel. I attempted to use calculated columns to calculate new dates for these deadlines 15 days after a date contained in another column, however these date calculations need to not include weekends and specific closed days at my institution. These can't be included in the date calculations because deadlines can't fall on those days or consider these closed days as a working day. After posting this issue about date calculations in the Microsoft Community for SharePoint, they have directed me to ask here because a workflow is apparently the best option for this.

For this date workflow, I will need it to pull the starting date from the SharePoint List (get items from SharePoint list) add 15 working days on (no weekends) and also not consider these specific closed days. The closed days could be contained in another list and also pulled by the workflow for the calculations. It would be a manual trigger, working at the start of the year to populate our trackers with the right information ready for usage across the year. This workflow will then populate the tracker that will then use these calculated dates to send off reminder emails when the 3 days before date is met. I know lists can send automated email notifications based on dates through the automate function in lists so hoping the automated emails should be possible. Just need to sort this date calculation niggle out first.

Any guidance or a basic plan of actions I need to include in the workflow would be much appreciated. 

Thanks :)

Categories:
Business Process Flows
  • David_MA Profile Picture
    David_MA 11,517 Super User 2025 Season 1 on at
    Calculating dates in a sharepoint list workflow
    I have not seen too many responses where anyone is going to take the time to provide enough detail to give you everything you are asking. My advice would be to start building your workflow, and then when you run into specific roadblocks, ask a limited question to get you through the hurdle until you have trouble with something else. To get you started, I would suggest:
    • Since your data is already in Excel, import your spreadsheet into the SharePoint site where you want to host the data to create your list. The data will need to be in a named table. Make sure the first column in the spreadsheet is a text field. It will be used as the Title field in the list and will be the one you click on to open/edit items.
    • Also make sure that the column names are short and concise to avoid any issues with your workflows. I would suggest renaming them using camel case and once imported into SharePoint you can rename them again with longer display names. When you import the spreadsheet, the current column names will become the internal names of the fields and cannot be changed and are limited to 30 characters. This is why you want them to be clear when you are dealing with them in workflows.
    • Then you will create a scheduled workflow that runs every day. You'll add a filter query to get the items that apply to your criteria to send the reminder before the deadline. Just note that filter queries on dates require the use of ISO8601 formatted dates, which is yyyy-MM-dd. Any other format will not work. Dates in SharePoint are stored in this format and in UTC, so you shouldn't have an issue from that standpoint.
     
    Once you've got that much done, if you have a specific question post it and it is always helpful to show a picture of your workflow and how the actions are configured. If there are error messages, show the action that generated the error and how it is configured along with the error message.
     
    Good luck!
  • Suggested answer
    SaiRT14 Profile Picture
    SaiRT14 1,988 Super User 2025 Season 1 on at
    Calculating dates in a sharepoint list workflow

    • Create the SharePoint Lists:

      • Main List: This will hold your tasks with a column for the start date.
      • Holiday/Closed Days List: This will contain the specific closed days (weekends can be excluded automatically in the workflow).

    • Create a Flow in Power Automate:

      • Trigger: Set up a manual trigger that you can run at the start of the year to calculate the dates for each task.
      • Action: Get Items from SharePoint:
        • Retrieve the tasks from your SharePoint List that contains the start date column.

    • Filter out Weekends and Closed Days:

      • Add a ‘Apply to Each’ loop to go through the items in the list.
      • Use the ‘Get Items’ action to retrieve the dates from your Holiday/Closed Days List.
      • You can exclude weekends using ‘Add to Date’ action with Workdays Only. To account for holidays, use a combination of condition actions to check if the calculated date falls on a holiday from the Closed Days List, then adjust accordingly.

    • Calculate Deadline Date (Excluding Weekends and Holidays):

      • Use expressions in Power Automate:
        • Add 15 working days to the start date while excluding weekends.
        • Compare the calculated date with the holidays fetched from the closed days list. If the calculated date falls on a holiday, add a condition to increment the date by 1 until a valid working day is found.

    • Store the Calculated Date:

      • Update the SharePoint List with the calculated deadline date for each task.

    • Automated Reminder Email 3 Days Before the Deadline:

      • Set another ‘Recurrence Trigger’ to run daily, checking for tasks where the deadline is within 3 days.
      • If the condition is met, use the ‘Send Email’ action to trigger a reminder email with the relevant details from the SharePoint list.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Upwork and Microsoft Power Platform Partnership
Welcome to the Power Platform Communities
Check out the New Agent Creator Community

Quick Links

Michael Gernaey – Community Spotlight

We are honored to recognize Michael Gernaey as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 566 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 516 Super User 2025 Season 1

#3
stampcoin Profile Picture

stampcoin 492

Last month Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans