SharePoint List Column Formula using YEAR and LOOKUP

Question

I have created a column named Quarter (Type - Calculated) in my List that I want to calculated based on a Date column (ex. 01/26/15). My formula is as follows:

="FY"&YEAR([Date]+274)&" Q"&LOOKUP(MONTH([Date]),{0,4,7,10;2,3,4,1})

I get a message

The formula contains a syntax error or is not supported.

I have tested the formula in excel and it works perfectly. I believe YEAR, MONTH and LOOKUP functions are all supported in SharePoint.

Am I missing a syntax error here? Any help would be much appreciated.

Answer 1

You can use below formula

=CONCATENATE("FY",YEAR(Date+274)," Q", IF(Month(Date)>10,1,IF(Month(Date)>7,4,IF(Month(Date)>4,3,2))))

Logic

1. CONCATENATE can be used to form formatted TEXT
2. First find FY - So here we can use the same Excel formula FY + Year(Date+274)
3. To calculate Quarter - We need to use IF statements. It take form If(Condition,TRUE,FALSE).

Answer 2

I don't remember Lookup being an allowable formula in the calculated column syntax. I think you'd need to replace those with Ifs. This is my go to article for calculated column references.

If you break the formula into separate pieces, you should see it work for both year and quarter. To make it easier, you can create the if branch in a separate column, then just merge in that column into your final formula. nesting ifs can get squirrelly to read.

Answer 3

I agree with Eric that LOOKUP is not a usable formula for calculated column. This is the formula I have used for displaying "Q# YYYY" format for a date column:

=IF(ISBLANK(TestDate),"",(CONCATENATE("Q"&(INT((MONTH(TestDate)-1)/3)+1)&" "&(YEAR(TestDate)))))