Forum Discussion
Auto Dates Update in SharePoint List
I'm glad to hear it. If you feel this was the solution, I'd greatly appreciate marking my answer as the solution. Thanks!
Would you mind to elaborate a little on the SharePoint list and give an example of the metadata? Is this year like a Year date column? If I understand you correctly, you are wanting to update all existing list items with a new year? For example, there is a column called "renewal date" that are all 2019, and you want this to automatically roll over to 2020? There are a few ways you could handle this depending on the configuration:
- Use an additional column that is of type calculated. If you had a column like "StartingDate", then create a new calculated column called "ExpirationDate" with a formula to either add 365 days from that date, or concatenate the current year plus month and day from the "StartingDate".
- Use Excel and Quick Edit you can use Excel to update via formula then copy/paste back into the SharePoint list.
- Write a Microsoft Flow with a trigger of once yearly on Jan 1 of that year to get all items in the list, for each item get the "ExpirationDate" and update the date to the current year.
If you give me a little more details of the list I can make a more pointed recommendation.
Thanks for the follow-up Doug!
The dates I have are dates associated with when a client's payroll is due, or when taxes need to be done etc. So each year, the date in terms of month and day remain the same but of course, the year needs to change for each new year. To you question (@If I understand you correctly, you are wanting to update all existing list items with a new year? For example, there is a column called "renewal date" that are all 2019, and you want this to automatically roll over to 2020?), yes, 100% correct.
I've attached a screenshot for you if that helps.
Great thanks for confirming. The best option here would be a Microsoft Flow. I did a quick test and in simplest terms it takes a trigger and 2 actions:
- Trigger - Run once yearly on Jan 1
- Get the items in the list
- For each item in the list, update the column for the item
I'm using an expression for the value but you could use OOTB actions for this. Here is my Flow:
The Start Date in my example is the column to update. I use the following expression:
addDays(items('Apply_to_each')?['StartDate'],365)You would need to update the column names (use the internal name of the column). Let me know if you have any questions. If this trigger runs in early morning, you might have to take time zone into account.DougThat worked perfectly! Thanks, Doug!
