locked
Defining a Specific Date in a Calculated Column Formula RRS feed

  • Question

  • I want to have a calculated column that does the following:  If Created is between 1/1/11 and 4/1/11, Output "Quarter 1 2011".  This will become a nested ELSE to have every quarter for the next few years, but I figured I'd simplify here.

    I'm able to do it if I use 2 extra columns to define 1/1/11 and 4/1/11 by using the below:

    =IF(AND(Created>[Begin Q1],Created<[Begin Q2]),"Quarter 1 2011","None"))

    It seems silly to have to use the extra columns, though.  How can I just define the dates within the calculated column?

    • Moved by Mike Walsh FIN Saturday, March 12, 2011 7:54 AM admin (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Friday, March 11, 2011 9:20 PM

Answers

  • Hi cjnolen,

     

    Try this on for size.  One calculated column:

    =CONCATENATE("Quarter ",ROUNDDOWN(((INT(MONTH([Created]))-1)/3),0)+1, " ",TEXT(YEAR([Created]),"000"))
    - Xenox G.
    - Xenox G.
    Friday, March 11, 2011 10:38 PM

All replies

  • Hi cjnolen,

     

    Try this on for size.  One calculated column:

    =CONCATENATE("Quarter ",ROUNDDOWN(((INT(MONTH([Created]))-1)/3),0)+1, " ",TEXT(YEAR([Created]),"000"))
    - Xenox G.
    - Xenox G.
    Friday, March 11, 2011 10:38 PM
  • OK, that worked beautifully!  It took me a bit to figure out the logic.

    Tuesday, March 15, 2011 6:44 PM
  • Hello Xenox,

    your formula works very well but I have found a strange behavior for the Calculated Column in the Library standard View... I try to explain better... the Calculated column get the right value but when I try to filter the column by clicking on its header in any standard view and selecting one values available (Q1, Q2, Q3, Q4), the view is refreshed only if I select ONLY ONE value (ex. Q2) but when I select 2 values (Q1 and Q2) the view doesn't refresh anymore, until I select only one value.

    Have you also found this issue? I'm using SP2013 Foundation...

    Many thanks in advance and best regards,

    Cristian

    Friday, August 8, 2014 1:57 PM