El Blanco's Office 2007 Blog

Monday, July 16, 2007

SharePoint Calculated Columns with Dates

I recently had a request to create a view on a list and group the view by the year and quarter of a particular date. i.e. the list in question has a date column, say "Start Date", and the view should be grouped by the year of the date e.g. 2007, and then by the quarter i.e. 1, 2, 3, or 4 depending on the month.

I did this by creating two new calculated columns on the list. The first column is called "Year", is of type "Single line of text", and has the formula shown below:

=TEXT(YEAR([Start Date]),"000")

I created this column as a single line of text instead of a numeric column because a numeric column would show the year "2007" as "2,007" with a comma in it which I didn't want.

The second column is called "Quarter", is of type "Number", and has the formula shown below:

=ROUNDDOWN(((INT(MONTH([Start Date]))-1)/3),0)+1

Hopefully someone will find these useful as there isn't a whole lot of information out there on the format and syntax of the calculated columns. However, some good examples can be found here.

posted by Chris White at 12:03 pm

12 Comments:

  • You rock! I was struggling to get rid of the 2,007 issue which I found occurred regardless of it being a text or numeric field (I tried both) using just the YEAR function. When I used your combination of TEXT with the YEAR function with a text field, it cured the issue!

    I've been hunting for an answer to this problem! Thank you!

    By Blogger Leesa, at 3:50 pm  

  • Thanks for this info.

    Do you have any idea how to display the Month Name like January, February etc. using Calculated Column in SharePoint?

    By Blogger Unknown, at 12:21 am  

  • Hi Amit,

    Not sure if there's an out-of-the-box function to do this, but if worst comes to the worst you could always use MONTH([Date]) to get the integer then use nested IF's to display the appropriate text.

    If I find anything further I'll post again !

    Cheers,
    Chris

    By Blogger Chris White, at 8:25 am  

  • Amit, you can use a CHOOSE command in a calculated column to display the months:

    =CHOOSE(MONTH([Created]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

    By Anonymous Anonymous, at 1:12 pm  

  • Hi..
    i have custom list in that date field having the date only.when i do workflow for sending an mail its showing the time also but i donot want the time.. i want to display the date only...
    is there any way to do that

    By Anonymous Anonymous, at 7:17 am  

  • Hi Anonymous,

    Yeah, you can customise the mail that is sent by setting the "HasCustomEmailBody" property to true and setting the "EmailBody" property to contain the desired email body(including DateTime.ToShortDateString() to get the date only). Both of these properties are members of the SPWorkflowTaskProperties class.

    Hope this helps,

    Chris

    By Blogger Chris White, at 8:02 am  

  • thank you!

    Withouth the TEXT function it shows the year with commas like 2,008.

    I was pulling my hair trying to find what went wrong. Thanks for that.

    By Anonymous Anonymous, at 9:47 pm  

  • Thanks for the TEXT formatting example!

    By Anonymous Anonymous, at 3:40 am  

  • hi,

    i would want to have a column 'Supervisor Due Date' in my list that is a calculated field with the following conditions:

    =IF([Status]="Pending", [Modified]+7, "")

    The thing is, whenever my workflow against that list sets the status from New to Pending, it populates the 'Supervisor Due Date' field but with value 1/6/1900. It does not display the correct data.

    Any thoughts about this?

    By Anonymous Anonymous, at 6:03 am  

  • If you want todays date the calculation is
    =TEXT([TODAY],"dddd-mm-yyyy")

    note you need to create a dummy column called TODAY and then delete it after you've set the formula
    be sure to set the column name and all references to that column in CAPITALS "TODAY" (no quotes)
    [TODAY]
    or you will get 1899

    By Anonymous Anonymous, at 3:59 pm  

  • Superb, just what I was hunting for
    Andy

    By Anonymous Anonymous, at 4:47 pm  

  • hey thanx for the trick of the year and quarter :)
    here are another one for sharepoint to display a month name from date field:

    To Display full month name e.g. “January”, “February”

    =TEXT([Date field],"MMMM")

    to display the short name: e.g. "Oct", "Nov":

    =TEXT([Joining Date],"MMM")

    By Anonymous Tarek Jajeh, at 1:09 pm  

Post a Comment

<< Home