I know, that title is very exciting right? Can’t wait to read more? Here is what it all means…
I had a need to create a “Days Open” column in an InfoPath Form Library that would display the amount of days a case has been open. This part is simple, I used a DATEDIF function in a Calculated column to calculate the amount of days between the “Created” date and another column called “Todays Date”.
That’s great, and it works – as long as each list item is updated daily; but that’s obviously not the case. I need the “Todays Date” column to be updated daily regardless of whether or not the list items are touched by the assignee. It seems like this kind of functionality should be out of the box – a date column with a default value of the [today] function should always be correct – right? Wrong!
I thought of a few different ways to do this, one being a custom workflow to run which would set “Todays Date” to [today] – but I still need a way for it to run daily. I could probably do that with a policy on the library. I could probably get a developer to write a timer job to kick-off the workflow daily too.. I could probably do it a bunch of ways…
But – I’m a PowerShell dude!
Sooo, here’s what I did do…
- Create a column called “Todays Date” which is a Date/Time column with a default value of [today]
- Create a column called “Days Open” which has the following calculated formula: =DATEDIF(Created,TodaysDate,”D”)
- Create the following PowerShell Script, save it as Update-SPListItemsWithTodaysDate.ps1 (Note: I also preserve the ‘Modified By’ values – as described in this post:
Start-SPAssignment -Global $SPWeb = Get-SPWeb http://site $List = $SPWeb.Lists["My Form Lib"] $Items = $List.Items foreach ($item in $items) { $modifiedBy = $item["Editor"] $formStatus = $item["Form Status"] if ($formStatus -ne "Rejected" -and $formStatus -ne "Completed") { $item["TodaysDate"] = Get-Date $item["Editor"] = $modifiedBy $item.Update() $list.Update() } } $SPWeb.Dispose() Stop-SPAssignment -Global - Create a Windows Scheduled Task on my server to run daily at 12:05 AM. This scheduled task is configured to launch powershell.exe and my Update-SPListItemsWithTodaysDate.ps1 script.
Note: I’ve added logic to my script so that if a column called “Form Status” equals “Rejected” or “Completed” then the script will ignore those items… Obviously adjust all of the values to your environment…
RD
SharePointRyan 
Hi!
How can I do something similar for SharePoint 2007?
Thank you.
Dave
Hi Dave,
I haven’t tested this fully, but if you replace lines 1 & 2 with the following code it should work:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | Out-Null
$SPSite = New-Object Microsoft.SharePoint.SPSite("http://YourSiteUrl"😉
$SPWeb = $SPSite.OpenWeb()
Also, you’ll need to remove line 17 as there are no SPAssignment Cmdlets with v3. You’ll want to add the following in it’s place:
$SPSite.Dispose()
Hope this helps!
Ryan
Hi Ryan, Thank you so much for your post I found it very helpful. I just have one quick question which I was hoping you could help me with. I want to maintain the modified date as that the column I want to work my calculation off. would it be possible to export the modified date in the same way as the modified by column or maybe just to update the todaysdate column and none other.
Any help/advice would be very much appreciated
Garc
Hello,
Good question. This would definitely be possible, here is some example code:
$modifiedBy = $item[“Editor”]
$modifiedDate = $item[“Modified”]
$item[“Modified”] = $modifiedDate
$item[“Editor”] = $modifiedBy
$item.Update()
Hope this helps!
Ryan
Ok, i know this post is a bit old but its the best suggestion so far. Spend days trying to figure out how to bypass this! Anyway
In a nutshell, I tweaked it a bit to get it working in our test environment and was able to update a new test list with 1 record. I then tried to run the script on the actual list that was already populated which had more than 1 record. The script worked, but only for the 1st record. It will not write to the subsequent ones?
The only noticeable change between this script and mine is the exclusion of the fields not used, and I also am using the $item.SystemUpdate() instead of $item.Update. For some reason the modified by field was being overridden which I did not want.
Long shot, but any ideas?
I just confirmed that this works for me, and is a more simplified version. This also uses SystemUpdate($false) which will keep the Modified and Modified By fields from getting updated by “System Account” as well as it will not increment the version (if versioning is enabled). Hope this helps!
$web = Get-SPWeb http://SomeUrl
$list = $web.Lists[“ListTitle”]
$list.Items | ForEach-Object {
$_[“TodaysDate”] = Get-Date #Replace TodaysDate with your field name
$_.SystemUpdate($false)
$list.Update()
}
$web.Dispose()
Thanks for that Ryan, as mentioned via twitter it worked fine. 🙂
Got another little tweak though and not sure if its possible, but is there any way using the above method to just count the business days in the week (i.e. exclude weekends)?
Well, without knowing exactly what you want to do – I’ll say that the Get-Date cmdlet has a Property called DayOfWeek which will tell you the day. So for example, if you were to run (Get-Date).DayOfWeek, it would return “Friday”. You could potentially wrap your code inside an IF statement to where it will only run and update if the date is not equal to Saturday or Sunday. Something like that should work.
Hi Ryan, great solution and I have been using it for some time with SP 2010. But, I have migrated to SP 2013 and I now have an issue with using it and am getting the following error:-
Specified cast is not valid.
At E:\Scripts\Update-SPListItemsWithTodaysDate.ps1:13 char:1
+ $item[“TestDate”] = Get-Date
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], InvalidCastException
+ FullyQualifiedErrorId : System.InvalidCastException
I’ve tried different settings on the date field such as standard or friendly but same result.
Any ideas?
Kelly, can you share all the PS code as well as your column name?
I need to create a column (Today) that updates daily, I’m gonna use this date with another calculated column.
But your script didn’t work with me. The column values are blank can you help me?
My script:
Start-SPAssignment -Global
$web = Get-SPWeb http://win08sp10:7359/
$List = $web.Lists [“TesteWorkflow”]
$Items = $List.Items
foreach ($item in $items)
{
$item[“TodayDate”] = Get-Date
$item.Update()
$list.Update()
}
}
$SPWeb.Dispose()
Stop-SPAssignment -Global
I’ve created the “TodayDate” column as a date/time column and put =[Today] inside the “Calculated Value” field
@Kelly : I had the same thing. Specified Cast is not valid. I resolved it by replacing the Get-Date with [System.DateTime]::Now. Still figuring out why that works and Get-Date doesn’t.