将日期更改为会计年度

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).

我想在Excel中编写一个函数来更改日期。逻辑是这样的:如果月份是(1月,2月或3月),结果显示我过去一年( - 1年),如果月份是(4月到12月),结果显示当前年份(当年日期显示)。

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

例如:如果日期是02,2012年1月,结果显示我2011年其他人给我看2012。

5 个解决方案

#1

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.

如果月份在第4个月之前,则减去1年,否则保持同一年。我不会将它转换为完整日期DD / MM / YYYY减去1年,以避免混淆,将其保持为年仅YYYY。

#2

=IF(MONTH(G3) >=4, YEAR(G3), YEAR(G3) - 1) where G3 is the date to test, is one way.

= IF(MONTH(G3)> = 4,YEAR(G3),YEAR(G3) - 1)其中G3是要测试的日期,是单向的。

#3

Please try:

=IF(OR(MONTH(A1)=1,MONTH(A1)=2,MONTH(A1)=3),2011,2012)

#4

With 02-Jan-2012 in A1 try,

随着2012年1月2日在A1尝试,

=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)

#5

Already plenty of answers, but thought I'd throw another one up:

已经有很多答案了,但我想再扔一个:

=YEAR(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))

#1