Presentation of fiscal year issue

[John44]

Hi,

I have a formula to derive a fiscal year from a date but it presents the fiscal year as one year say 2021. Whereas I would like to be presented as 2021/22. The formula is = Year(B5)+MONTH(B5)>=C5) when B5 is the date and C5 is the start month. I suspect the answer might be in format - cells - custom perhaps?

Thanks

John

 

[levipe]

The formula you posted seems to be missing something. I suspect it's something like =YEAR(B5)+IF(MONTH(B5)>=C5,1,0). You can change that to

=LET(y,YEAR(B5)+(--(MONTH(B5)>=C5)),y&"/"&RIGHT(y+1,2))

Note that the result isn't a number, but is text.

 

[John44]

 

[John44]

Apologies. That was the first formula I tried that didn't work. Here's the one that does work above. I need the formula to display '2021/22' rather than '2022' or "2021/22' as I've hard coded in C3 for illustrative purposes i.e. the formula works as shown in cells c5,c6 etc but it's a presentation thing. Maybe in format cells custom somewhere?

 

[levipe]

Replace your formula with the following:

=LET(y,YEAR(DATE(YEAR(B4),MONTH(B4)+(9-1),1)),y&"/"&RIGHT(y+1,2))

 

[John44]

Thanks. It nearly works as below as May 2023 should be 2023/24 rather than 2024/25.

 

[Biz]

Hi John,

I believe the dates highlighted in the year have the wrong year. I have corrected them.

Please refer to a simpler formula solution.

Presentation of fiscal year issue (Awesome).xlsx
	A	B	C	D	E	F	G	H	I	J
1			Fin Year	Need					End FY	3
2		30/04/2021	2021/22	2021/22	TRUE	2021/22
3		31/05/2022	2022/23	2022/23	TRUE	2023/24
4		30/06/2022	2022/23	2022/23	TRUE	2023/24
5		30/07/2022	2022/23	2022/23	TRUE	2023/24
6		30/08/2022	2022/23	2022/23	TRUE	2023/24
7		31/05/2023	2023/24	2023/24	TRUE	2024/25
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=C2=D2
F2:F7	F2	=LET(y,YEAR(DATE(YEAR(B2),MONTH(B2)+(9-1),1)),y&"/"&RIGHT(y+1,2))
C2:C7	C2	=IF(MONTH(B2)<$J$1,YEAR(B2)-1&"/"&RIGHT(YEAR(B2),2),YEAR(B2)&"/"&RIGHT(YEAR(B2)+1,2))

Does it help?

Kind Regards,

Biz

 

[Phuoc]

Or try this

Book1
	B	C	D
1			5
2	4/26/2023	2022/23
3	5/26/2023	2023/24
4	12/27/2023	2023/24
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=TEXT(EDATE(B2,-$D$1+1),"yyyy/")&TEXT(EDATE(B2,12-$D$1+1),"yy")

 

[Biz]

Going back to post 7
More streamline solution without if statement
=YEAR(B2)-IF(MONTH(B2)<$J$1,1,0) & "/" & RIGHT(YEAR(B2)+IF(MONTH(B2)<$J$1,0,1),2)

Biz

 

[John44]

Going back to post 7
More streamline solution without if statement
=YEAR(B2)-IF(MONTH(B2)<$J$1,1,0) & "/" & RIGHT(YEAR(B2)+IF(MONTH(B2)<$J$1,0,1),2)

Biz

Problem here is that it ignores the fiscal year ending in Nov. Nov 22 should be 2021/22 and Dec 22 is 2022/23. This just follows the calender year?