3

I have 2 columns sDate ( Start Date ), eDate ( End Date ) in my list. The functionality I need

if(eDate == "" )
 print sDate // Nov 10
else if(MONTH(sDate) == MONTH(eDate))
 print sDate("mmm dd") + eDate(" - dd")  // Nov 10 - 17
else
 print sDate("mmm dd") + eDate(" - mmm dd") // Nov 10 - Dec 10

Orignal: 

=IF(eDate="",TEXT(sDate,"mmm dd"),CONCATENATE(TEXT(sDate,"mmm dd")," ",(TEXT(eDate,"- dd"))))

I tried:

=IF(eDate="",TEXT(sDate,"mmm dd"),(IF(MONTH(sDate)=MONTH(eDate),CONCATENATE (TEXT(sDate,"mmm dd"), " ", TEXT(eDate,"- dd")),CONCATENATE (TEXT(sDate,"mmm dd"), " ", TEXT(eDate,"- mmm dd"))))

I get this exception, which is not very helpful:

The formula contains a syntax error or is not supported.
at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
at Microsoft.SharePoint.Library.SPRequest.UpdateField(String bstrUrl, String bstrListName, String bstrXML)

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
Vabs
  • 485
  • 1
  • 5
  • 17
  • Not directly answering your question but you may find this useful - http://blog.pentalogic.net/2011/05/sharepoint-calculated-column-cheat-sheet/ – Ryan Oct 17 '11 at 13:47

1 Answers1

2

Add one more closing parenthesis ) at the end of the expression.

Excel is useful to debug SharePoint calculated column expressions - I have pasted your expression into Excel spreadsheet, replaced sDate and eDate with addresses of cells containing example dates and Excel suggested a correction automatically.

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
  • 2
    You can also use a programmers editor like Notepad ++, it will highlight matching pairs of opening/closing parenthesis. – Ryan Oct 17 '11 at 13:46
  • @Marek - thanks for letting me know about the missing `)` . I was pulling out my hairs to find a problem in expression. – Vabs Oct 18 '11 at 02:24
  • @Ryan: A big advantage of Excel in this scenario is the fact that it can evaluate SharePoint expressions. – Marek Grzenkowicz Oct 18 '11 at 06:22