Calculating dates in a sharepoint list workflow

(0) Share

Report

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:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (1)

Report
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.

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report
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!

Was this reply helpful? Yes No