Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sharepoint IF statement using dates 1

Status
Not open for further replies.
Aug 24, 2005
56
US
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.
 
nevermind. I answered my own question.

IF(([start date] > "6/30/2010"+0), "FY11", "FY10")
 
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 -
Hope that helps.


carl
MCSD, MCTS:MOSS
 
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])))

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top