# Find Leftmost cell in defined range and return column distance.

#### dovl

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=IFERROR(TODAY()-INDEX(\$B\$1:\$R\$1,AGGREGATE(15,6,(COLUMN(\$B\$1:\$R\$1)-COLUMN(\$B\$1)+1)/(B2:R2<>""),1)),"")``

#### dovl

##### New Member
Hi & welcome to MrExcel.
Excel Formula:
``=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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### MrExcel MVP, Moderator
The 15 tells the function to work the same way as Small & the 6 is to ignore errors.

#### dovl

##### New Member

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

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

#### dovl

##### New Member
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

##### MrExcel MVP, Moderator
Excel Formula:
``=IFERROR(COLUMN(\$R\$2)-AGGREGATE(15,6,(COLUMN(\$B\$1:\$R\$1)-COLUMN(\$B\$1)+1)/(B2:R2<>""),1),0)``

Replies
3
Views
70
Replies
8
Views
355
Replies
0
Views
72
Replies
5
Views
99
Replies
3
Views
113

1,129,352
Messages
5,635,785
Members
416,882
Latest member
ericvrealty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back