13 thoughts on “Use PowerShell to update a [today] column daily using a Scheduled Task

    • Ryan Dennis

      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&quot😉
      $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

  1. garc66

    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

    • Ryan Dennis

      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

  2. dsanchez78

    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?

    • Ryan Dennis

      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()

  3. dsanchez78

    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)?

    • Ryan Dennis

      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.

  4. Kelly (@kelsnz)

    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?

  5. Ian Viana

    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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s