Find Leftmost cell in defined range and return column distance.

[dovl]

I am looking for a formula that can tell me the "days since activation"(number found in column "U") . Each number represents "times used on given day", if blank, that means it wasn't used that day. I want this formula to work even when there will be gaps of data, indicating non use (e.g. Row 4). I Highlighted Yellow the cells which represent the activation of device (first use). In this example I counted the amount of cells (days) through column R.

Since I have the dates listed on Row 1, a second formula that would be helpful is, to find the leftmost cell from defined range (activation date) then grab the corresponding date from row 1 and return number of days to "todays date" and disregard the date in column R.

Any assistance will be greatly appreciated.

Thanks

 

[dovl]

Ok, how about

=IFERROR(COLUMN($R$2)-AGGREGATE(15,6,(COLUMN($B$1:$R$1)-COLUMN($B$1)+1)/(B2:R2<>""),1),0)

Amazing! Thank you very much!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dovl]

Ok, how about

=IFERROR(COLUMN($R$2)-AGGREGATE(15,6,(COLUMN($B$1:$R$1)-COLUMN($B$1)+1)/(B2:R2<>""),1),0)

Hi Fluff, would you be kind enough to help me with one more formula on this? I'm looking for a formula to calculate the span of days between the first use and last use. In the pasted image, I'm looking for a formula for column V. In row 7, first date of activity was 1/23/2021, it was dormant for a few days and then had activity through 02/01/2021. So span of days of activity (1.23 through 2.1) is 10 days. Can you help me with a formula that searches for first day, and then provides the span of days through last day of use? Much Appreciated

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks

 

[dovl]

I have a follow up question on the formula provided above "=IFERROR(COLUMN($DD$2)-AGGREGATE(15,6,(COLUMN($E$1:$DD$1)-COLUMN($E$1)+1)/(E2:DD2<>""),1),0)" I applied it to a larger data set (pasted below) and it seems it comes back 3 numbers higher then it should. Any idea why? I noticed this, because I used a separate formula to find the span of days. But that formula should equal the same as my requested formula, when the device is used through the last day (column) of the data set. and it seems the formula in Column DH consistently yields 3 numbers higher then it should. Thank you.

De-identified Copy.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN
1	Device ID	2021-01-17	2021-01-18	2021-01-19	2021-01-20	2021-01-21	2021-01-22	2021-01-23	2021-01-24	2021-01-25	2021-01-26	2021-01-27	2021-01-28	2021-01-29	2021-01-30	2021-01-31	2021-02-01	2021-02-02	2021-02-03	2021-02-04	2021-02-05	2021-02-06	2021-02-07	2021-02-08	2021-02-09	2021-02-10	2021-02-11	2021-02-12	2021-02-13	2021-02-14	2021-02-15	2021-02-16	2021-02-17	2021-02-18	2021-02-19	2021-02-20	2021-02-21	2021-02-22	2021-02-23	2021-02-24	2021-02-25	2021-02-26	2021-02-27	2021-02-28	2021-03-01	2021-03-02	2021-03-03	2021-03-04	2021-03-05	2021-03-06	2021-03-07	2021-03-08	2021-03-09	2021-03-10	2021-03-11	2021-03-12	2021-03-13	2021-03-14	2021-03-15	2021-03-16	2021-03-17	2021-03-18	2021-03-19	2021-03-20	2021-03-21	2021-03-22	2021-03-23	2021-03-24	2021-03-25	2021-03-26	2021-03-27	2021-03-28	2021-03-29	2021-03-30	2021-03-31	2021-04-01	2021-04-02	2021-04-03	2021-04-04	2021-04-05	2021-04-06	2021-04-07	2021-04-08	2021-04-09	2021-04-10	2021-04-11	2021-04-12	2021-04-13	2021-04-14	2021-04-15	2021-04-16	2021-04-17	2021-04-18	2021-04-19	2021-04-20	2021-04-21	2021-04-22	2021-04-23	2021-04-24	2021-04-25	2021-04-26	2021-04-27	2021-04-28	2021-04-29	2021-04-30	Total	To date	Count	Through 4.30	Span of days				Diff	# of cycles
2	1			2	3	2	3	3	3	3	3	5	3	3	3	3	3	3	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	2	3	3	3	3	3	3	3			2	3	3	2	3	3	3	3	1	3	3	3	3	3	4	3	3	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	4	3	3	1	4	3	2	297	108	100	105	102	Why arent they equal			5	3
3	2																												1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	76	83	73	80	77				7	2
4	3																																			1	1	1	1	1	1	1	1	1		1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1																																									30	76	28	73	30				45	1
5	5																										2	2	2	2	2	3	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	2	2	2	2	2							159	85	73	82	73				9	2
Meter Counts

Cell Formulas
Range	Formula
DF2:DF5	DF2	=IFERROR(TODAY()-INDEX($E$1:$DD$1,AGGREGATE(15,6,(COLUMN($E$1:$DD$1)-COLUMN($E$1)+1)/(E2:DD2<>""),1)),"")
DG2:DG5	DG2	=COUNT(E2:DD2)
DH2:DH5	DH2	=IFERROR(COLUMN($DD$2)-AGGREGATE(15,6,(COLUMN($E$1:$DD$1)-COLUMN($E$1)+1)/(E2:DD2<>""),1),0)
DI2:DI5	DI2	=IFERROR(SUM(AGGREGATE({14,15},6,COLUMN(E2:DD2)/(E2:DD2>0),1)*{1,-1})+1,0)
DN5,DN2:DN3	DN2	=ROUNDDOWN((DH2/30),0)
DM2:DM5	DM2	=DH2-DG2

 

[Fluff]

Because you first column is now 3 columns to the right of where it was originally ie col E instead of col B

 

[dovl]

Because you first column is now 3 columns to the right of where it was originally ie col E instead of col B

Thank you. I didn't fully understand the formula but I just copied and modified per my new sheet. I did however, change the range to reflect columns E through DD for the formula vs your original range of B through R. Shouldn't that address the issue of starting 3 columns in? If not, how can I modify the formula to accommodate added rows before the table.

Much appreciated.

 

[Fluff]

As long as the dates in row 1 are always sequential you can use this

=IFERROR($DD$1-AGGREGATE(15,6,$E$1:$DD$1/(E2:DD2<>""),1)+1,0)