Length of stay in a financial year

[queysoft]

Hello. I have been looking at a a formula that can automatically calculate something i do manually. However I cannot find a one formula fits all solution. Its to do with the length of stay of 2 different types of animals in a financial year (starting April 1). Column A has the animal type and column B the arrival date. Column C has the departure date when they leave. Line 2 shows animal 1 arrived February but left on April 15, so the length of stay is 15 days this financial year. Line 3 is 10 days and for line 4/5/6 its 26 days, 11 and then 6.

Can there be a formula that would populate column D automatically given those conditions?

Thanks for any help. It will save me time everytime the boss asks!!

 

[Peter_SSs]

For the future, consider this for your sample data provision so helpers do have to manually type sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this in D2 and copy down

=IF(C2="",TODAY(),C2)-MAX(B2,DATEVALUE("1 April"))+1

 

[AlanY]

how about?

Book1
	A	B	C	D
1
2	1	01/02/2022	15/04/2022	15
3	2	01/03/2022	10/04/2022	10
4	1	01/04/2022	15/04/2022	15
5	2	01/04/2022		26
6	1	15/04/2022		12
7	2	20/04/2022		7
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(ISBLANK(C2),TODAY(),C2)-MAX(B2,DATE(2022,4,1))+1

 

[queysoft]

Hello and thank you for your prompt reply. Yes, I was aware of the add in (and that would be my choice do do so) however I am on the works laptop right now and adding that in has been restricted. However I note your comment. I did not know about the 'test here' so that's good knowledge.

As for the solution, yes it works perfectly - thank you for that!

Many thanks and will endeavour to go down the add in route in future!!

Mark.

 

[queysoft]

how about?

Book1
	A	B	C	D
1
2	1	01/02/2022	15/04/2022	15
3	2	01/03/2022	10/04/2022	10
4	1	01/04/2022	15/04/2022	15
5	2	01/04/2022		26
6	1	15/04/2022		12
7	2	20/04/2022		7
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(ISBLANK(C2),TODAY(),C2)-MAX(B2,DATE(2022,4,1))+1

Hi - Yes, that works too!!

Many thanks, I need to get to grips with the more advance "basic" formula!!

 

[Peter_SSs]

As for the solution, yes it works perfectly - thank you for that!

You're welcome. Thanks for the follow-up.

however I am on the works laptop right now and adding that in has been restricted.

As a second choice, you can just copy/paste from your worksheet like below. At least we can copy something from that.

Arrival Date	Departure date	LOS
01-Feb​	15-Apr​	15​
01-Mar​	10-Apr​	10​
01-Apr​	15-Apr​	15​
01-Apr​		26​
15-Apr​		12​
20-Apr​		7​