SharePoint calculated field: comparing two dates for FY calc

0
votes

FY runs Oct-Sep, I have a list with a date field, and I am simply looking to create a formula for a calculated FY field, that basically says what FY the date represents.

I started trying this nested IF like this:

=IF(Year([DateField] > 11; =IF(Month([DateField] > 9; 12);....

but alas I fear I am going the wrong with this. MSDN showed calculating the diff between two dates, and adding days, weeks or months to the date, but I have not really found anything that could help with this.

All help is greatly appreciated as always.

1
sharepointformulacalculated-columnsdate-arithmetic

1 Answers

1
votes

This works for Calculating FY as "YY" in SharePoint where the fiscal year starts on October 1 and StarTime is the date feild: =IF(MONTH(StartTime)>9,ROUNDUP((YEAR(StartTime)+1-2000),0),ROUNDUP((YEAR(StartTime)-2000),0))

        Yeilds "13" for this FY.

Derived from the Quarter calculating function at http://www.wssdemo.com/Lists/Resources/DispForm.aspx?ID=1266&ContentTypeId=0x01005C6956D831EB0B49B4AEE28DF95A05820C00268EA96B67FC8F448ED6072B5D14B7CF

See related at http://abstractspaces.wordpress.com/2009/05/02/common-date-time-formulas-for-sharepoint-calculated-fields/