Change Column status to Expiring
I’m creating a training tracker as a SharePoint list. Within the list I have a date column for when a course is going to require a refresher, which is a calculated column based of Date of Training + Refresher period.
If I set up a choice column with the following options – In Date, Expiring and Expired, how can I achieve the following:
When a refresher date is 30 days away the status changes to ‘Expiring’
Step 1: Create a New Scheduled Flow
Log into Power Automate at powerautomate.microsoft.com.Click on Create > Scheduled flow.Name the flow (e.g., “Expiring Training Flow”) and set the recurrence to Daily:Trigger: Recurrence.Frequency: 1 day.
Step 2: Get Items from SharePoint
Add a New Step > Get Items from SharePoint.
Site Address: Select your SharePoint site.List Name: Choose your list that contains the RefresherDate and Status columns.
Do not apply any filter here — you will handle this in the flow.
Step 3: Initialize Variables for Date Ranges
We will initialize variables to store the dates in dd-MM-yyyy format for comparison with the RefresherDate.
Variable 1: Today’s Date
Add a New Step > Initialize Variable.Name: TodayDateType: StringValue:
This sets today’s date in dd-MM-yyyy format.
Variable 2: 30 Days from Today
Add another Initialize Variable.Name: ExpiringStartDateType: StringValue:
This sets the date 30 days from today in dd-MM-yyyy format.
Step 4: Apply to Each (Loop through SharePoint Items)
Add a New Step > Apply to Each.In the Select an output from previous steps, choose value from the dynamic content of the Get Items step (this will loop through each item in the list).
Step 5: Add Condition to Ensure RefresherDate Is Not Null
Before comparing the dates, ensure that the RefresherDate is not null.
Inside the Apply to Each, add a Condition:First value: items(‘Apply_to_each’)?[‘RefresherDate’]Condition: is not equal toSecond value: Leave empty (this checks if RefresherDate is not null).
Step 6: Add Condition to Check If RefresherDate is Between Today and 30 Days
Now we will add a condition to check if the RefresherDate is between today and 30 days from today.
Inside the If Yes branch (after checking for null), add a New Condition to compare the RefresherDate to today’s date and 30 days from today.
Condition 1 (Check if RefresherDate is less than or equal to 30 days from today):
First value:
Condition: is less than or equal toSecond value: variables(‘ExpiringStartDate’)
AND
Condition 2 (Check if RefresherDate is greater than or equal to today):
First value:
Condition: is greater than or equal toSecond value: variables(‘TodayDate’)
This ensures that the RefresherDate is between today and 30 days away, in dd-MM-yyyy format.
Step 7: Update the Status to “Expiring”
In the If Yes branch (if the RefresherDate is within the range), add an Update Item action to update the status.
Site Address: Choose your SharePoint site.List Name: Select your SharePoint list.ID: Use the ID from the dynamic content in the Apply to Each loop.Status: Set the Status column to “Expiring”.
Hi I’m creating a training tracker as a SharePoint list. Within the list I have a date column for when a course is going to require a refresher, which is a calculated column based of Date of Training + Refresher period.If I set up a choice column with the following options – In Date, Expiring and Expired, how can I achieve the following:When a refresher date is 30 days away the status changes to ‘Expiring’ AI gave me the solution below, however it keeps failing. Any help greatly appreciated. Step 1: Create a New Scheduled FlowLog into Power Automate at powerautomate.microsoft.com.Click on Create > Scheduled flow.Name the flow (e.g., “Expiring Training Flow”) and set the recurrence to Daily:Trigger: Recurrence.Frequency: 1 day.Step 2: Get Items from SharePointAdd a New Step > Get Items from SharePoint.Site Address: Select your SharePoint site.List Name: Choose your list that contains the RefresherDate and Status columns.Do not apply any filter here — you will handle this in the flow.Step 3: Initialize Variables for Date RangesWe will initialize variables to store the dates in dd-MM-yyyy format for comparison with the RefresherDate.Variable 1: Today’s DateAdd a New Step > Initialize Variable.Name: TodayDateType: StringValue: plaintextCopy codeformatDateTime(utcNow(), ‘dd-MM-yyyy’)This sets today’s date in dd-MM-yyyy format.Variable 2: 30 Days from TodayAdd another Initialize Variable.Name: ExpiringStartDateType: StringValue: plaintextCopy codeformatDateTime(addDays(utcNow(), 30), ‘dd-MM-yyyy’)This sets the date 30 days from today in dd-MM-yyyy format.Step 4: Apply to Each (Loop through SharePoint Items)Add a New Step > Apply to Each.In the Select an output from previous steps, choose value from the dynamic content of the Get Items step (this will loop through each item in the list).Step 5: Add Condition to Ensure RefresherDate Is Not NullBefore comparing the dates, ensure that the RefresherDate is not null.Inside the Apply to Each, add a Condition:First value: items(‘Apply_to_each’)?[‘RefresherDate’]Condition: is not equal toSecond value: Leave empty (this checks if RefresherDate is not null).Step 6: Add Condition to Check If RefresherDate is Between Today and 30 DaysNow we will add a condition to check if the RefresherDate is between today and 30 days from today.Inside the If Yes branch (after checking for null), add a New Condition to compare the RefresherDate to today’s date and 30 days from today.Condition 1 (Check if RefresherDate is less than or equal to 30 days from today):First value: plaintextCopy codeformatDateTime(items(‘Apply_to_each’)?[‘RefresherDate’], ‘dd-MM-yyyy’)Condition: is less than or equal toSecond value: variables(‘ExpiringStartDate’)ANDCondition 2 (Check if RefresherDate is greater than or equal to today):First value: plaintextCopy codeformatDateTime(items(‘Apply_to_each’)?[‘RefresherDate’], ‘dd-MM-yyyy’)Condition: is greater than or equal toSecond value: variables(‘TodayDate’)This ensures that the RefresherDate is between today and 30 days away, in dd-MM-yyyy format.Step 7: Update the Status to “Expiring”In the If Yes branch (if the RefresherDate is within the range), add an Update Item action to update the status.Site Address: Choose your SharePoint site.List Name: Select your SharePoint list.ID: Use the ID from the dynamic content in the Apply to Each loop.Status: Set the Status column to “Expiring”. Read More
