Calculate days between two dates but from the fiscal year if start date precedes April

[SaraWitch]

Hello peeps.

I am trying to work out the number of nights in the fiscal year between two dates (a start and an end date), as well as having the total number of days overall (i.e., if the start date precedes a date in April). I also want it to calculate to today as the end date if the end date is blank.

I can calculate the overall total number of days, no problem (

=IF(A2="","",IF(B2>0,B2-A2,TODAY()-B2))

), but I'm having difficulty in calculating the fiscal number of days.

My start date is in A2, my end date is in B2 and my fiscal year date in C1. The formula I'm using (which isn't working!) is:

=IF(A2="","",IF(A2<C1,IF(B2>0,B2-C1,TODAY()-C1)))

Any help would be gratefully received...

 

[awoohaw]

Try this. But, I'm not sure why you say nights? Is this something that is different from days, Are you wanting to include the last day of the sequence in the count?

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F
1	Start	End	Fiscal Nights		Fiscal Yr Start	2023-04-01
2	2023-06-13	2023-07-30	47
3	2023-04-20	2023-08-04	106
4	2023-05-05	2023-08-23	110
5	2023-03-04	2023-07-23	113
6	2023-04-03	2023-08-07	126
7	2023-04-04	2023-08-14	132
8	2023-02-14	2023-07-26	116
9	2023-01-02	2023-07-23	113
10	2023-05-03	2023-08-02	91
11	2023-02-11	2023-07-01	91
12	2023-05-03		188
13	2023-02-11		220
SaraWitch

Cell Formulas
Range	Formula
C2:C13	C2	=IF($B2="",TODAY(),$B2)-IF($F$1<=$A2,$A2,$F$1)

 

[SaraWitch]

That works perfectly; thank you, @awoohaw! And, apologies - nights and days same thing (in this case anyway!).

Thank you!

 

[awoohaw]

Happy to help.
Best Wishes!