1,804 articles and 14,981 comments as of Wednesday, May 11th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

NothingButSharePoint.com
Monday, January 18, 2010

Create Order in a SharePoint Date Column

2010-0-19-STPStump the Panel contained an interesting exchange over the past few days on how to sort a date column by month… interesting enough to recreate here.

ihmunro
I have date column where I have a number of entries that show just the month and the year. My problem is trying to the the months to order correctly, i.e Jan, Feb, Mar etc. What I have is Apr, Aug, Dec etc

Is the a quick way to do this using code, or would jquery be able to help here?

sympmarc, Moderator
You have a few choices:

  • Create a Calculated column which translates from the text value to the numeric value
  • Use a Data View Web Part (DVWP) and use the ddwrt:FormatDate function
  • jQuery, but I think this is a poor use of jQuery given the above two options
  • It sort of depends on where you’re talking about using the value.

    ihmunro
    I am using the values to group by month – hence the reason I need them in the correct order.

    George W
    The quickest way is via Calculated column, e.g.:

    =text( [DateInQuestion], "yyyy-mm (MMM)" )

    Yielding 2009-01 (Jan), etc.

    chasepes
    I have also used:

    =IF([Date]="","Your message",TEXT([Date],"yyyy-mm (MMMM yyyy)"))

    or

    =IF([Date]="","",TEXT([Date],"yyyy-mm (MMMM yyyy)"))

    Don’t know if your application is like mine, but my items are not guaranteed to have a date therefore I put in the initial check if the field is empty.

    I also used a “belt & suspenders” approach to showing the date “2009-10 (October 2009)” since some users still love to see the name of the month.

    ihmunro
    This works just great – many thanks.

    Note from Mark Miller: This goes on daily on the Stump the Panel: SharePoint Q&A Forums. The interesting part of this exchange, however, is that it isn’t moderators who are providing the final answers, but George and Charlie who have grown in their SharePoint abilities enough to step in and help with a final solution.

    Very gratifying to watch. Very gratifiying.

     

    Please Join the Discussion

    5 Responses to “Create Order in a SharePoint Date Column”
    1. George W says:

      Mark:

      You should feel very proud. Your work on E U SP and otherwise has helped me immensely.

      • George,

        Yes, it has grown a bit since you first joined us, hasn’t it. I’ve still got a lot I’d like to accomplish, but I’m pretty happy with the way things are turning out.

        Rergards,
        Mark

    2. AlbertBisbal says:

      Hi!

      If you want to rename the months in other languages or less letters..

      =IF(YEAR([Fecha])>1900,YEAR([Fecha])&” – “&CHOOSE(MONTH([Fecha de Contabilización]),”01 (Enero)”,”02 (Febrero)”,”03 (Marzo)”,”04 (Abril)”,”05 (Mayo)”,”06 (Junio)”,”07 (Julio)”,”08 (Agosto)”,”09 (Septiembre)”,”10 (Octubre)”,”11 (Noviembre)”,”12 (Diciembre)”),”Sin Fecha”)

    3. Larry Nardolillo says:

      I am trying to create a calculated column using calculated fields from other info in the same library. It seems that many of my ideas require formula writing in the Additional Column Setttings section. Unfortunately, my formula writing ability maxes out at =sum(a2:a7).

      Is there a resource for learning how to write formulas? I have found plenty that start at the Intermediate level or talk about asp.net or jquery or action script. I don’t know what they are. I need a beginners text. Think of it as a book for a liberal arts major.

      • Larry – Dessie Lunsform has written an extensive series of articles, 40 to 50 articles, exactly on that topic: Taming the Elusive Calculated Column. Use the Topics link in the menu above and select Dessie Lunsford at the filter under Authors. — Mark


    Notify me of comments to this article:


    Speak and you will be heard.

    We check comments hourly.
    If you want a pic to show with your comment, go get a gravatar!