SharePoint: Group by Year or Month in a View

 

The following is for SharePoint 2007, SharePoint 2010 and SharePoint Online.

 

Grouping by Year

Grouping by year is pretty straight forward, just add a calculated column to your list to display the year and then group on that column in a view.

Steps:

1. Add a new column to your list or library
   1. 2007: Click Settings and then Create Column
      2010: In the ribbon click the List or Library tab and click Create Column
   2. Select “Calculated column (calculation based on other columns)”
       
   3. Enter this formula: 
        =YEAR([yourdatecolumn])      example: =YEAR([Due Date])
       
   4. Create or edit a view and in the Group By section select your new calculated column
       
   5. Set the return type to text:
       
   6. Save the view and test

       

 

Two Problems!

What’s with the 1899 year and why the commas? The commas are because SharePoint, in spite of our selecting “Single line of text”, still thinks the digits are a number. The 1899 year is from items with no date entered.

Fixing the commas…

Easy, just force the result to be text by prefixing the year with an empty string:   “”
=  “”  &  YEAR([Due Date])

   

Fixing the 1899 / no date problem…

Just add an ”IF” to the formula to test for the date.

    =IF(  [Due Date]="" ,  "No Due Date",  ""&YEAR([Due Date])  )

   

The result:

 

If you want the “No Due Date” listed first, then just add a space before “No”:

 

 

 

 

Grouping by Month

The Group By option in a view groups on the entire field. There is no way to group on a part of a field, such as just the month and the year of a date. We can get there by creating a calculated column or two and then grouping on the calculated columns.

We can pull the Month using a formula similar to the one above by using MONTH(). You will need both the year and the month and as SharePoint will sort from left to right you will need to build a string that looks something like “2012/02”, “2012 02” or “2012 / 02”.

When we combine this with the “empty date IF” from above you will get something like this:

  =IF([Due Date]="","No due date",YEAR([Due Date])&"/"&RIGHT("0"&MONTH([Due Date]),2))

 

The final view:

   

 

Both Year and Month?

If you wanted to group on Year and then on Month you can:

1. Create both columns described above
   Month:  =IF(  [Due Date]="", "No due date", YEAR([Due Date])&"/"&RIGHT("0"&MONTH([Due Date]),2))
   Year:     =IF(  [Due Date]="", "No Due Date", ""&YEAR([Due Date])  )
2. Create a view and first group on Year and then group on Month

The result:

   

 

Year, Month and Day?

Sorry, but SharePoint views only support two levels of grouping. If you really need to do this then you can use SharePoint Designer to create a Data View Web Part to group to any number of levels. See here: http://techtrainingnotes.blogspot.com/2011/01/sharepoint-group-by-on-more-than-2.html

 

 

Group Headings

If you want to get rid of the group heading then see this article:

http://techtrainingnotes.blogspot.com/2009/06/sharepoint-removing-group-headings-from.html

That article is for SharePoint 2007. There are both 2007 and 2010 versions available in my book.

    

.

Book’s done!

 

I thought I’d write a little book. I had two weeks of vacation time… Take some of the better stuff from the blog, clean it up and polish it, and may be have 250 pages of content…

Now ten months later I have 400 pages of content and enough stuff for a second book. I can’t let go of this thing! I finally did one final pass of edits and uploaded it to the publisher!

So what did I end up with? This:

• 401 pages
• 304 screen captures
• 83,338 words (including sample code)
• 3879 lines of sample JavaScript, jQuery and CSS
• 504,721 characters
• a ridiculous number of hours
• At least (I have not counted yet) 75 customizations and tricks that will work in WSS, MOSS, SharePoint Foundation and SharePoint Server. And most should work in Office 365.

 

Book’s done!

The family is relieved… they have not seen me much at all lately. Work, study, teach and then go write - repeat.

Now I can:

• take some time off
• fly some model airplanes
• take a ride in a Tri-Motor Ford with my sons (did that today!) (may have to go back on Sunday and do it again)
• start another book……………

 

Book’s done!

If you like the articles in this blog, then you should really like the book. More tricks and tips. More details on each one. Both 2007 and 2010 versions for almost every example.

You can order it here today: https://www.createspace.com/3471790

Discount! And as you are a reader of my blog, for at least the next 10 days until the end of July you can go to the link above and get a 25% discount by using this code: 77ULP6VH

 

And on Amazon with free shipping! 

 

 

.

SharePoint: Edit date and time in one minute increments

 

When working with SharePoint date fields you are offered a Date Picker that let’s you select time to the nearest five minutes.

While this works fine for most tasks and meetings, some manufacturing processes need the time set to the minute.

.

 

I first tried some quick JavaScript tricks to change the dropdown list to include 00 to 59, but it appears that the server side code only expects the dropdown to only have five minute steps and that causes an error on save. The next experiment was to create custom New and Edit forms to allow free form edits. Turns out you don’t have to do anything special to the Display form or the views as they just display whatever was entered.

 

The steps below generally follow what you will find in other blog sites or this MSDN article, expect for replacing the Date Picker fields with Text Box fields.

 

Test this on a “test” list before duplicating on a production list!

 

List Preparation

Before we can enter times, you need to configure your list to support Date and Time. Simply go to the list’s properties and edit the date field to support Date & Time.

 

Also add a Description to your date fields with a sample of an acceptable date. Remember that the user no longer has a date picker to make sure they select a valid date.  Something like: “Enter dates as mm/dd/yyyy hh:mm AM/PM”

 

Disable Attachments!

     

The following edits will not work if Attachments are enabled for the list. Go to List Settings, Advanced and disable attachments. I think this error is caused by having two web parts on the same page, each adding some common ID that confuses the page’s JavaScript  (I did a search and found this article, http://support.microsoft.com/kb/953271, but I did not try it’s solution.)

 

Fire Up SharePoint Designer!

SharePoint 2007 (and SPD 2007) example follows – 2010 is farther down the page…

Edit the “New” form  (NewForm.aspx)

SharePoint 2007 steps:

1. Open SharePoint Designer and open your site   “http://yourserver/sites/.yoursite”
2. In the Folder List expand Lists and your list
3. Double-click NewForm.aspx (you may want to make a copy of this file first)
4. Display the page in Design view
      
5. Right-click the existing web part and click Web Part Properties
6. Expand the Layout section and click Hidden and then click OK
      
   Note: Do not delete the existing web part!
7. Click below the existing web part (you may need to experiment to find a spot)
8. In the menu click Insert, SharePoint Controls and Custom List Form
         
9. Select your list (not just any list with a similar name, your list!)
       
10. Select the correct content type
11. Click New Item Form and click OK
12. Wait until the fields load
13. Click on the date field you want to change
14. If the “Common FormField Tasks” popup is not displayed, right-click the web part and select Show Common Control Tasks
       
15. Change the Format As to “Text Box”
16. Repeat for any other date fields you need to change (Start Date and Due Date for a task list)
    
    Note: In my test environment, SharePoint Designer 2007 occasionally locks up here. You may have to try this more than once.
    
17. Save your changes and click Yes if prompted about “customize a page”.
       
18. Go to a browser and add a new item to the list
         

Note: Several different date formats are acceptable for date entries:
     

 

Edit the “Edit” form  (EditForm.aspx)

SharePoint 2007 steps:

1. Follow steps 1 – 11 above except double-click EditForm.aspx and select “Edit Item Form”
2. If the fields are not displayed:
1. Right-click the new web part
2. Click Show Common Control Tasks
3. click the Show With Sample Data checkbox
4. click Refresh Data View.



3. Continue with step 13 from above…

You were probably expecting something like this:

   

and got this instead:

   

Go back to the EditForm.aspx page, switch to Code view and search for this line (my example uses @StartDate):

    <asp:TextBox runat=“server” id=”…” text=”{@StartDate}” …

And then replace @StartDate with

    ddwrt:FormatDateTime(string(@StartDate), 1033, 'G')

The result should look like this

    <asp:TextBox runat=“server” id=”…” text="{ddwrt:FormatDateTime(string(@StartDate), 1033, 'g')}"

Now test and confirm that it now looks like this:

       

 

 

 

Edit the “New” form  (NewForm.aspx)

SharePoint 2010 steps:

1. Open SharePoint Designer and open your site   “http://yourserver/sites/.yoursite”
2. In the Navigation section click Lists and Libraries
3. Double-click your your list
4. In the Forms section double-click NewForm.aspx (you may want to make a copy of this file first)
5. Display the page in Design view
      
6. Right-click the existing web part and click Web Part Properties
      
7. Expand the Layout section and click Hidden and then click OK
     
   Note: Do not delete the existing web part!
8. Click below the existing web part (you may need to experiment to find a spot)
9. In the Insert ribbon click SharePoint (in the Controls section) and Custom List Form
      
10. Select your list (not just any list with a similar name, your list!)
       
11. Select the correct content type
12. Click New Item Form and click OK
13. Wait until the fields load
14. Click on the date field you want to change
15. If the “Common FormField Tasks” popup is not displayed, right-click the web part and select  Show Common Control Tasks
       
16. Change the Format As to “Text Box”
17. Repeat for any other date fields you need to change (Start Date and Due Date for a task list)
18. Save your changes
19. Go to a browser and add a new item to the list
         

Note: Several different date formats are acceptable for date entries:
     

 

 

Edit the “Edit” form  (EditForm.aspx)

SharePoint 2010 steps:

1. Follow steps 1 – 13 above except double-click EditForm.aspx and select “Edit Item Form”
2. If the fields are not displayed:
1. Click the “tab” above the new web part:
    
2. Click the Design tab in the Data View Tools section of the ribbon
3. click the Sample Data checkbox

 

3. Continue with step 14 from above…

You were probably expecting something like this:

   

and got this instead:

   

Go back to the EditForm.aspx page, switch to Code view and search for this line (my example uses @StartDate):

    <asp:TextBox runat=“server” id=”…” text=”{@StartDate}” …

And then replace @StartDate with

    ddwrt:FormatDateTime(string(@StartDate), 1033, 'G')

The result should look like this

    <asp:TextBox runat=“server” id=”…” text="{ddwrt:FormatDateTime(string(@StartDate), 1033, 'g')}"

This edit will display “A.D.” in the Design view, but will display the correct data in the browser.

   

Now test and confirm that it now looks like this:

       

 

 

 

 

Hours, Minutes and Seconds?

 

SharePoint will store time to the second. There are no additional changes needed for the NewForm.aspx page, and EditForm.aspx only needs the “g” in the FormatDateTime changed to “G”. The real work will be in also creating a custom DispForm.aspx and custom views.

     text="{ddwrt:FormatDateTime(string(@StartDate), 1033, 'G')}"

 

.

SharePoint: Past Due / Late Tasks in a Task List

 

Update: If you have users who display dates in formats other than MM/DD/YYYY you will find that the following will not work consistently. Here’s is some background on the date problem in SharePoint and JavaScript and how to fix it: http://techtrainingnotes.blogspot.com/2011/03/sharepoint-javascript-and-dates.html (includes replacement code for this article)

 

The code below and in the above link works with both SharePoint 2007 and 2010.

 

Here’s another JavaScript trick to deal with a common request, highlighting past due tasks in a SharePoint Task List.

Here’s a few non-JavaScript ways that this has been done:

• Create a custom workflow (too much work and not portable, but it can also send reminder emails!)
• Use a trick to add a calculated field using [Today] (only works when the trick is reapplied every day or each task is manually edited)

This example uses a bit of copy-and-paste JavaScript and requires the following:

• A Content Editor Web Part to hold the JavaScript
• Some JavaScript you can copy from below
• An extra column to hold the past due message (optional)

The goal:

 

To add the extra column:

If you would like to display a message such as “Past Due” or display an image then you will need an extra column, preferably one your users cannot edit. A calculated column that displays a blank will do this.

• Add a new column to your list (Settings, New Column)
• Give it a title (any will do) such as “Past Due”
• Make the column a Calculated column and set the formula to ="" (and equal sign followed by two quotes)

To add the Content Editor web part and JavaScript:

• Display the task list view to color code (each view will need its own web part and JavaScript)
  
• Add a Content Editor web part
  
  • Site Actions, Edit Page
  • Add a Content Editor web part and move it below the calendar web part
  • In the web part, click Edit, Modify Shared Web Part, and in the Appearance section change "Chrome" to "None".
    
• Add the JavaScript
  
  • Click the Source Editor button
  • Type or paste the JavaScript (examples below)
  • Count the columns displayed in your task list to find the “Due Date” and “Past Due” columns. The Attachments column is column zero and the Due Date is typically column five (0,1,2,3,4,5).
  • Edit the JavaScript to set these two numbers:
    var colDueDate = 5;
    var colPastDue = 7; // set to -1 to ignore
  • Click Save, OK and Exit Edit Mode to see the results
    
  • If you would like to display an image in place of the text change this line:
    
    x[i].parentNode.childNodes[colPastDue].innerHTML='Past due!'
    
    to:
    
    x[i].parentNode.childNodes[colPastDue].innerHTML='<img src="/_layouts/images/ERRLG.GIF" />'
    
    and set your SRC to an image in your library or in the SharePoint install folder (“images”).

Making this work in views other than “All Tasks”

You probably will first test this in the “All Tasks” view and then switch to one of the other task views and mumble something about Mike’s hacks don’t work everywhere!

Well… each view page is, well… it’s own page. Check the URLs.

All Tasks: http://…../AllItems.aspx
Active Tasks: http://…../active.aspx
My Tasks: http://……/MyItems.aspx

Each page will need it’s own Content Editor Web Part with the JavaScript, the extra column will need to be displayed and the column numbers in the JavaScript adjusted.

As an example, here are the steps for the “Active Tasks” view:

• Modify the view to display the “Past Due” column
• Check your column numbers for the “Due Date” and “Past Due” columns
• Add the Content Editor Web Part and the JavaScript as described above, adjusting the column numbers as needed.

Bugs? Issues?

• You cannot sort the “Past Due” column as this data is not in SharePoint. It has only been added in the user’s browser. (Another plus for a workflow solution?)
• Each new view created will need the CEWP and the JavaScript added.
• Changing or reordering the columns in a view will require an update to the column numbers in the JavaScript.
• As the CEWP is not visible, you will probably forget that it is there, and then wonder why the color highlighting no longer works after you modify the view!
• As the CEWP is not visible, the next person to inherit your SharePoint site will have no idea how this works. (You do document everything you do in your site, right?)

The JavaScript:

<script type="text/javascript" language="javascript">

  // Stolen from techtrainingnotes.blogspot.com !

  // this routine needs to know:
  // column with the Due Date (attachment column is #0)
  //      (in the default task list Due Date is #5)
  // optional column to display the "past due" message
  //      (can be any "single line of text" column)
  var colDueDate = 5;
  var colPastDue = 7; // set to -1 to ignore

  var i=0;
  d=new Date() //current date/time

  var x = document.getElementsByTagName("TD") // find all of the TDs
  for (i=0;i<x.length;i++)
  {
    if (x[i].className=="ms-vb2") //find the TDs styled for lists
    {
      //find a row with a key phrase
      if (x[i].innerHTML=="Not Started"
           | x[i].innerHTML=="In Progress"
           | x[i].innerHTML=="Waiting on someone else" )
      {
        if (d.getTime() >= Date.parse(x[i].parentNode.childNodes[colDueDate].childNodes[0].innerHTML) )
        {
        x[i].parentNode.style.backgroundColor='red'; // set the color
        if (colPastDue > -1)
        x[i].parentNode.childNodes[colPastDue].innerHTML='Past due!' 
        }

      }

    }

  }
</script>