Find Leftmost cell in defined range and return column distance.

dovl

New Member
Joined
Apr 6, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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

1617717750876.png
 

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
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=IFERROR(TODAY()-INDEX($B$1:$R$1,AGGREGATE(15,6,(COLUMN($B$1:$R$1)-COLUMN($B$1)+1)/(B2:R2<>""),1)),"")
 
Solution

dovl

New Member
Joined
Apr 6, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
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
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 6, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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!
 

dovl

New Member
Joined
Apr 6, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
I'm afraid I don't understand, what do you mean you requested two formulae?
 

dovl

New Member
Joined
Apr 6, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IFERROR(COLUMN($R$2)-AGGREGATE(15,6,(COLUMN($B$1:$R$1)-COLUMN($B$1)+1)/(B2:R2<>""),1),0)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top