Search code examples
exceldateif-statementexcel-formulaexcel-2007

Change the date to fiscal year

I want to write a function in Excel to change the date. The logic is like this: if the month is (Jan, Feb or March) the result show me one year past (-1 year) and if the month is (April to -December) the result show the current year (which year the date shows).

example: if date is 02,Jan,2012 the result show me 2011 else show me 2012.

Solution

  • To extract fiscal year use:

    =YEAR(A1) + IF(MONTH(A1)>=4,1,0)

    I think in your case you would need:

    =YEAR(A1) - IF(MONTH(A1)>=4,0,1)

    If the months is before 4th month then subtract 1 year, else keep the same year. I wouldn't convert it to a full date DD/MM/YYYY with a 1 year subtracted, to avoid confusion keep it as year only YYYY.