Determine Date Range

[rwmill9716]

I have a table that extends several thousand rows. I've included an image showing what I'm trying to do. Cols A and D are trade entry and exit dates; Col B is a unique symbol for a sequence of trades; Col C marks the beginning of the trade with an "*". I need a formula for Col E which calculates the number of days in the trade. Using A_2 as an example, the Days in the Trade would be the Max date for that symbol in Col D minus the Min date for that symbol in Col B; here, that's 8/20/24 - 2/1/24 or 201 days. Note, in the real spread sheet, the symbols will be mixed over a wide range of rows; I'm only interested in presenting the Days in Trade for rows housing an "*".

 

[rwmill9716]

Note, I'm using EXCEL 2013 so I don't have access to the MaxIFs function.

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Date Start	SYMBOL	Trade Begin	Day End	Days in Trade
2	1/1/2024	A_1	*	1/2/2024	19
3	1/5/2024	A_1		1/15/2024
4	1/10/2024	A_1		1/20/2024
5	2/5/2024	B_1	*	4/5/2024	126
6	3/5/2024	B_1		6/10/2024
7	2/1/2024	A_2	*	2/1/2024	201
8	2/16/2024	A_2		8/20/2024
9	2/17/2024	A_2		2/25/2024
10	6/5/2024	A_2		7/25/2024
Sheet6

Cell Formulas
Range	Formula
E2:E10	E2	=IF(C2="*",MAX(IF($B$2:$B$10=B2,$D$2:$D$10))-MIN(IF($B$2:$B$10=B2,$A$2:$A$10)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that this is an array formula. You need to enter it using Control+Shift+Enter, not just enter.

 

[rwmill9716]

=IF(C2="*",MAX(IF($B$2:$B$10=B2,$D$2:$D$10))-MIN(IF($B$2:$B$10=B2,$A$2:$A$10)),"")

Thanks, Eric, this formula works fine.