Sharepoint IF statement using dates 1

[Cordeiro82]

Hello All,

I am trying to create a calculated column using an existing column that contains dates.

The statement is as follows : IF([start date] > 6/30/2010, "FY11", "FY10")

However, sharepoint does not seem to be reading 6/30/2010 as a date and keeps giving me FY11 as the return value even if [start date] = 6/1/2010

How can I solve this problem?

Thanks.

 

[Cordeiro82]

nevermind. I answered my own question.

IF(([start date] > "6/30/2010"+0), "FY11", "FY10")

 

[dinger2121]

try using the datevalue function -

=IF([start date]>DATEVALUE("6/30/2010"),"FY11","FY10")

If you create a calculated column and just set it as =[start date], I think you will see a serialized representation of the date (eg. 36,210). You cannot do a comparison on that with "6/30/2010". Instead, serialize your comparison data also using the datevalue function then compare.

MS documentation -

http://office.microsoft.com/en-us/sharepointtechnology/HA011609821033.aspx

Hope that helps.


carl
MCSD, MCTS:MOSS

 

[Cordeiro82]

Thanks Carl.

Both formulas ended up working in SharePoint.

I did later realize that the dates in the list spanned more than two fiscal years so I expanded the formula to fit any date range.

=IF(MONTH([start date])>=7,CONCATENATE("FY",(YEAR([start date])+1)),CONCATENATE("FY",YEAR([start date])))