SOLVED

Auto Dates Update in SharePoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-754096%22%20slang%3D%22en-US%22%3EAuto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754096%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20clients%20in%20an%20accounting%20firm%20that%20all%20have%20fixed%20dates%20for%20various%20reasons.%26nbsp%3B%20Once%20the%20client%20is%20entered%20into%20the%20list%20with%20their%20corresponding%20dates%20for%20this%20year%20(2019)%20I%20want%20to%20avoid%20having%20to%20go%20back%20into%20the%20list%20again%20and%20update%20the%20year%20for%202020.%26nbsp%3B%20Is%20there%20a%20way%20I%20can%20update%20these%20dates%20automatically%20at%20the%20start%20of%20each%20new%20year%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20and%20look%20forward%20to%20any%20guidance%20you%20may%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-754096%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754141%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376174%22%20target%3D%22_blank%22%3E%40Workplaces%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20mind%20to%20elaborate%20a%20little%20on%20the%20SharePoint%20list%20and%20give%20an%20example%20of%20the%20metadata%3F%26nbsp%3B%20Is%20this%20year%20like%20a%20Year%20date%20column%3F%26nbsp%3B%20If%20I%20understand%20you%20correctly%2C%20you%20are%20wanting%20to%20update%20all%20existing%20list%20items%20with%20a%20new%20year%3F%26nbsp%3B%20For%20example%2C%20there%20is%20a%20column%20called%20%22renewal%20date%22%20that%20are%20all%202019%2C%20and%20you%20want%20this%20to%20automatically%20roll%20over%20to%202020%3F%26nbsp%3B%20There%20are%20a%20few%20ways%20you%20could%20handle%20this%20depending%20on%20the%20configuration%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EUse%20an%20additional%20column%20that%20is%20of%20type%20calculated.%26nbsp%3B%20If%20you%20had%20a%20column%20like%20%22StartingDate%22%2C%20then%20create%20a%20new%20calculated%20column%20called%20%22ExpirationDate%22%20with%20a%20formula%20to%20either%20add%20365%20days%20from%20that%20date%2C%20or%20concatenate%20the%20current%20year%20plus%20month%20and%20day%20from%20the%20%22StartingDate%22.%3C%2FLI%3E%3CLI%3EUse%20Excel%20and%20Quick%20Edit%20you%20can%20use%20Excel%20to%20update%20via%20formula%20then%20copy%2Fpaste%20back%20into%20the%20SharePoint%20list.%3C%2FLI%3E%3CLI%3EWrite%20a%20Microsoft%20Flow%20with%20a%20trigger%20of%20once%20yearly%20on%20Jan%201%20of%20that%20year%20to%20get%20all%20items%20in%20the%20list%2C%20for%20each%20item%20get%20the%20%22ExpirationDate%22%20and%20update%20the%20date%20to%20the%20current%20year.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIf%20you%20give%20me%20a%20little%20more%20details%20of%20the%20list%20I%20can%20make%20a%20more%20pointed%20recommendation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754808%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F5855%22%20target%3D%22_blank%22%3E%40Doug%20Allen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20follow-up%20Doug!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20dates%20I%20have%20are%20dates%20associated%20with%20when%20a%20client's%20payroll%20is%20due%2C%20or%20when%20taxes%20need%20to%20be%20done%20etc.%26nbsp%3B%20So%20each%20year%2C%20the%20date%20in%20terms%20of%20month%20and%20day%20remain%20the%20same%20but%20of%20course%2C%20the%20year%20needs%20to%20change%20for%20each%20new%20year.%26nbsp%3B%20To%20you%20question%3CEM%3E(%40%3C%2FEM%3E%3CSPAN%3E%3CEM%3EIf%20I%20understand%20you%20correctly%2C%20you%20are%20wanting%20to%20update%20all%20existing%20list%20items%20with%20a%20new%20year%3F%20For%20example%2C%20there%20is%20a%20column%20called%20%22renewal%20date%22%20that%20are%20all%202019%2C%20and%20you%20want%20this%20to%20automatically%20roll%20over%20to%202020%3F)%3C%2FEM%3E%2C%20yes%2C%20100%25%20correct.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI've%20attached%20a%20screenshot%20for%20you%20if%20that%20helps.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755900%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376174%22%20target%3D%22_blank%22%3E%40Workplaces%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat%20thanks%20for%20confirming.%26nbsp%3B%20The%20best%20option%20here%20would%20be%20a%20Microsoft%20Flow.%26nbsp%3B%20I%20did%20a%20quick%20test%20and%20in%20simplest%20terms%20it%20takes%20a%20trigger%20and%202%20actions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ETrigger%20-%20Run%20once%20yearly%20on%20Jan%201%3C%2FLI%3E%3CLI%3EGet%20the%20items%20in%20the%20list%3C%2FLI%3E%3CLI%3EFor%20each%20item%20in%20the%20list%2C%20update%20the%20column%20for%20the%20item%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI'm%20using%20an%20expression%20for%20the%20value%20but%20you%20could%20use%20OOTB%20actions%20for%20this.%26nbsp%3B%20Here%20is%20my%20Flow%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123337i85248718CFA66AE0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Flow%20to%20set%20days.png%22%20title%3D%22Flow%20to%20set%20days.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Start%20Date%20in%20my%20example%20is%20the%20column%20to%20update.%26nbsp%3B%20I%20use%20the%20following%20expression%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Eadd%3C%2FSPAN%3E%3CSPAN%3EDays%3C%2FSPAN%3E%3CSPAN%3E(item%3C%2FSPAN%3E%3CSPAN%3Es%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E'Apply_to_each'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E%3F%5B%3C%2FSPAN%3E%3CSPAN%3E'StartDate'%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%3C%2FSPAN%3E%3CSPAN%3E365%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EYou%20would%20need%20to%20update%20the%20column%20names%20(use%20the%20internal%20name%20of%20the%20column).%26nbsp%3B%20Let%20me%20know%20if%20you%20have%20any%20questions.%26nbsp%3B%20If%20this%20trigger%20runs%20in%20early%20morning%2C%20you%20might%20have%20to%20take%20time%20zone%20into%20account.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EDoug%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756761%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756761%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20worked%20perfectly!%26nbsp%3B%20Thanks%2C%20Doug!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756811%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376174%22%20target%3D%22_blank%22%3E%40Workplaces%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20glad%20to%20hear%20it.%26nbsp%3B%20If%20you%20feel%20this%20was%20the%20solution%2C%20I'd%20greatly%20appreciate%20marking%20my%20answer%20as%20the%20solution.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756841%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756841%22%20slang%3D%22en-US%22%3EDone!%20Sorry...brand%20new%20to%20the%20forum.%20Thanks%20again!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756845%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Dates%20Update%20in%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376174%22%20target%3D%22_blank%22%3E%40Workplaces%3C%2FA%3E%2C%26nbsp%3Bno%20problem%20always%20glad%20to%20help.%26nbsp%3B%20If%20you%20need%20any%20help%20on%20Flow%20in%20the%20future%20be%20sure%20to%20check%20out%20the%20dedicated%20%3CA%20href%3D%22https%3A%2F%2Fpowerusers.microsoft.com%2Ft5%2FMicrosoft-Flow-Community%2Fct-p%2FFlowCommunity%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFlow%20community%3C%2FA%3E!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, there.

 

I have a list of clients in an accounting firm that all have fixed dates for various reasons.  Once the client is entered into the list with their corresponding dates for this year (2019) I want to avoid having to go back into the list again and update the year for 2020.  Is there a way I can update these dates automatically at the start of each new year?

 

Thanks and look forward to any guidance you may have.

 

 

7 Replies
Highlighted

@Workplaces,

 

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:

 

  1. 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".
  2. Use Excel and Quick Edit you can use Excel to update via formula then copy/paste back into the SharePoint list.
  3. 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.

Highlighted

@Doug Allen 

 

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.

 

 

Highlighted

@Workplaces,

 

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:

 

  1. Trigger - Run once yearly on Jan 1
  2. Get the items in the list
  3. 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:

Flow to set days.png

 

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.  
Doug
Highlighted

That worked perfectly!  Thanks, Doug!

Highlighted
Best Response confirmed by Workplaces (New Contributor)
Solution

@Workplaces,

 

I'm glad to hear it.  If you feel this was the solution, I'd greatly appreciate marking my answer as the solution.  Thanks!

Highlighted
Done! Sorry...brand new to the forum. Thanks again!
Highlighted

@Workplaces, no problem always glad to help.  If you need any help on Flow in the future be sure to check out the dedicated Flow community