# Defining a Specific Date in a Calculated Column Formula

• ### 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 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

• 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