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

 

[Fluff]

Hi & welcome to MrExcel.
How about

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

 

[dovl]

Hi & welcome to MrExcel.
How about

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

Hi Fluff, Thank you for the quick response, and for welcoming me.
I pasted your suggested formula in cell U2 and it yielded 77 instead of 15.

 

[Fluff]

That's because you said

return number of days to "todays date" and disregard the date in column R.

which is what it does

 

[dovl]

Whoops you are correct. My apologies. I was focusing on the first formula I requested, which would yield the result based on the date ranges listed on the spread sheet.

Also, in your suggested formula, what does the 15,6, represent? I receive this report weekly, and the rows and columns (range) keeps expanding. I recorded a macro of my weekly formatting and formulas and am curious if this formula can be entered in a way that it is fluid to expansion of ranges. (for instance when I enter a vlookup function, I will start from cell A1, click Ctrl Shift right key, and then click left key twice. This way no mater how many columns get entered, my formula will always look for last cell and go back to the third to last column)

I hope I'm not asking for too much assistance.

Thank you so much!

 

[Fluff]

The 15 tells the function to work the same way as Small & the 6 is to ignore errors.

AGGREGATE Function

The Excel AGGREGATE function returns a aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).

exceljet.net

 

[dovl]

Thank you. This was my first post and perhaps I wasn't supposed to request two formulas in one post. I hope its ok if I don't mark the question as answered so I can still receive responses to my first formula in need.

 

[Fluff]

I'm afraid I don't understand, what do you mean you requested two formulae?

 

[dovl]

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

My apologies. In my first paragraph, I was requesting a formula that doesn't necessarily reference days or dates, rather a formula that finds the left most cell in a defined range and lets me know how many cells away it is from my defined end point. In this example, the defined end point will be column R. In other words, I want this formula to work even if there are no dates entered in Row 1, rather it will count the cells until column R. This way the result will remain constant relative to when the report was generated.

 

[Fluff]

Ok, how about

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