Calculate number of days between two dates when one is created by another formula

[SaraWitch]

Hello peeps,

I am trying to calculate the number of days/nights between two dates (A2 & B2) and use a further date (D2) if the latter (B2) is empty. However, the further date (D2) is created by a formula, which doesn't work in my final formula (E2).

Fiscal nights formula.xlsx
	A	B	C	D	E
1	Date placed	Date ended	Fiscal year	Year start date	Number of nights
2	03/11/2023	04/11/2023	2023/24	03/04/2023	215
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(A2="","",IF(MONTH(A2)<=3,(YEAR(A2)-1)&"/"&RIGHT(YEAR(A2),2),YEAR(A2)&"/"&RIGHT((YEAR(A2)+1),2)))
D2	D2	=IF(A2="","",IF(C2="2022/23","04/04/2022",IF(C2="2023/24","03/04/2023")))
E2	E2	=IF(A2="","",IF(B2=0,TODAY(),B2)-IF(D2<=A2,A2,D2))

Any help would be appreciated!

 

[Phuoc]

C2 change to:

=IF(A2="","",IF(C2="2022/23",DATE(2022,4,4),IF(C2="2023/24",DATE(2023,4,4))))

Or:

=IF(A2="","",DATE(LEFT(C2,4),4,4))

 

[SaraWitch]

Works a treat (changed in D2)! Thank you so much, @Phuoc!