Changing just the year for all rows in a date column

As the title says I need to change the year for all rows in a list to 2015 from 2014 but leave the month and day the same for each.

Had this been built as a calculated date column it would be much easier but it’s just a normal date column and I can’t figure out if what I want to do is possible.

Any help is greatly appreciated!




You can either use PowerShell or use CSOM for doing this.
– Amal Hashim
Jan 9 ’15 at 0:18



Open id Datasheet View(if 2010 version), or quick-edit mode if 2013, and ctrl+c, ctrl+v values there.
– Gennady G
Jan 9 ’15 at 12:52


2 Answers


You can follow below example using PowerShell

#Load necessary module to connect to SPOService
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client.Runtime”) | Out-Null

#Login Information for script
$User = “
$Pass = “password”

$WebUrl = “”

#Connect to SharePoint Online service
Write-Host “Logging into SharePoint online service.” -ForegroundColor Green

$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User, (ConvertTo-SecureString $Pass -AsPlainText -Force))

#Get the Necessary List
Write-Host “Getting the required list.” -ForegroundColor Green
$List = $Context.Web.Lists.GetByTitle(“VacationRequestForm”)

$Query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery(100);
$Items = $List.GetItems($Query);


#Edit existing list items
foreach($item in $Items)
$item[“DateColumn”] = ([datetime]$item[“DateColumn”]).AddYears(1);

Write-Host “Your changes have now been made.” -ForegroundColor Green



Works great. I changed up the item limit on the createallitemsquery() method. But other than that this works great!! Thanks Amal!
– Braden
Jan 9 ’15 at 18:15

You could try opening the list in Excel or Access and making the changes there.

To do this, open the list in your browser (needs to be IE), from the ribbon click the List tab and then click either Export to Excel or Open in Access.



Wouldn’t you have to re-import the spreadsheet and create a new list using this method?
– Braden
Jan 9 ’15 at 18:39



Certainly not if opened in Access, as I’ve done it via this method before. I presumed the same for Excel, although I don’t have experience with this method.
– Submits
Jan 9 ’15 at 18:53