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.
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.
=IF(MONTH(G3) >=4, YEAR(G3), YEAR(G3) - 1) where G3 is the date to test, is one way.
Please try:
=IF(OR(MONTH(A1)=1,MONTH(A1)=2,MONTH(A1)=3),2011,2012)
With 02-Jan-2012 in A1 try,
=YEAR(A1)-(MONTH(A1)<4)
For a full date use one of these,
=DATE(YEAR(A1)-(MONTH(A1)<4), MONTH(A1), DAY(A1))
=EDATE(A1, -(MONTH(A1)<4)*12)
Already plenty of answers, but thought I'd throw another one up:
=YEAR(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))
