Share via

Custom List - Calculated Column that Uses Date Ranges

Question

Tuesday, August 7, 2012 3:35 PM

I am attempting to create a calculated column within a custom list that will assign a value based on the Created date falling within a certain date range.  Ideally there would be multiple date ranges and associated assigned values.  I have included the concept below in written in plain English.

If [Created] is between 07/01/2012 and 09/30/2012 enter "2013 - Qtr 1" or
If [Created] is between 10/01/2012 and 12/31/2012 enter "2013 - Qtr 2" or
If [Created] is between 01/01/2013 and 03/31/2013 enter "2013 - Qtr 3" or
If [Created] is between 04/01/2013 and 06/30/2013 enter "2013 - Qtr 4" or
If [Created] is between 07/01/2013 and 09/30/2013 enter "2014 - Qtr 1" or
If [Created] is between 10/01/2013 and 12/31/2013 enter "2014 - Qtr 2" or
If [Created] is between 01/01/2014 and 03/31/2014 enter "2014 - Qtr 3" or
If [Created] is between 04/01/2014 and 06/30/2014 enter "2014 - Qtr 4"
etc.

I am using SharePoint 2010, IE8, Windows 7.  I am not the sys admin and do not think that I can make changes to Java. Also I am not well versed with VB therefore I was hoping for a solution that would use a formula within a calculated column (not sure if this is possible).  Any help is appreciated.  Thank you!

All replies (4)

Tuesday, August 7, 2012 10:23 PM âś…Answered | 1 vote

You could make it more generic. Maybe add a few more calculated columns just to simplify it.

A fiscal year calculated column  =IF(DATE(YEAR(DT),7,1)>DT,YEAR(DT),YEAR(DT)+1) where DT is the date field you're testing. It's using 7/1 as the beginning of your year

A quarter calculated column

=IF(MONTH(DT)>=10,"Qtr 2",IF(MONTH(DT)>=7,"Qtr 1",IF(MONTH(DT)>=4,"Qtr 4","Qtr 3")))

Then another calculated column to blend the 2.

Robin

Tuesday, August 7, 2012 4:15 PM | 1 vote

Hi

you can do that using web interface and calculate it using IF() formula

like

IF((AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1","No it isn't)

and you will replace as you need No it isn't with another line of

IF((AND([created]>="next min date",[Created]=<"next max date"),"2013-Qtr 1","No it isn't")

So the forumal will look like

IF((AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",

IF((AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2 ","...possible next condition")

)

Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

Tuesday, August 7, 2012 5:17 PM

Thank you!  I tried using this recommendation and also the variations shown below but kept getting syntax errors.  Any ideas what I am doing wrong.  Prior to posting I had attempted something similar but got the same results also.  Thanks again!

  1. As presented
    =IF((AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",IF((AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2"))

  2. As presented with an additional argument
    =IF((AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",IF((AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2",""))

  3. As presented with additional closing parentheses
    =IF((AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",IF((AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2"))))

  4. As presented with additional closing parentheses and an additional arguement
    =IF((AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",IF((AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2",""))))

  5. Removing the double parentheses in favor of single between the IFs and ANDs
    =IF(AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",IF(AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2"))

  6. Removing the double parentheses in favor of single between the IFs and ANDs with an additional arguement
    =IF(AND([created]>"07/01/2012",[Created]=<"09/30/2012"),"2013-Qtr 1",IF(AND([created]>"10/01/2012",[Created]=<"12/31/2012"),"2013-Qtr2",""))

Thursday, August 9, 2012 4:43 PM

That is it!  Thank you Robin In OR for the solution and Romeo Donca for helping me edge closer!  I really appreciate your help!

  • Last updated on