Continuous date subtraction in a row of cells?

RachelJ

New Member
Joined
May 20, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hi team! I'm keeping track of my plant watering schedule in Excel, and have two questions.

I have rows of cells indicating watering dates per plant, and want to create a formula at the end of each row that subtracts the most recent watering date from today. Is there a way to automatically have Excel seek the most recent/farthest right cell, so I don't have to manually change which cell it points to every time I water? AND

I would like to find out the average number of days between waterings. How to I tell Excel to subtract each earlier date in a row from its immediately subsequent cell (next date) and average the whole series? ie =AVERAGE(B2-A2, C2-B2, D2-C2...)

Thanks so much! This forum and the Youtube channel are fantastic. :)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to the forum!

Try:

Book1
ABCDEFGHIJKLMNOPQ
1Days since last waterAverage days between watering
2Ficus1-Apr4-Apr20-Apr1-May10-May18-May29.4
3Rhododendron21-Apr30-Apr2-May15-May19-May17
4Lily19-Apr22-Apr29-Apr17-May39.333333
Sheet35
Cell Formulas
RangeFormula
P2:P4P2=TODAY()-LOOKUP(2^999,B2:N2)
Q2:Q4Q2=AVERAGE(IF(C2:O2<>"",C2:O2-B2:N2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
A little slow off the mark but another way.....

Book1
ABCDEFG
1PlantDays Since last Average BeteweenWater Dates>>
2Plant1174101/01/202005/02/202008/02/202003/05/2020
3Plant2161422/03/202006/04/202016/04/202004/05/2020
4Plant3154103/01/202007/02/202010/02/202005/05/2020
5Plant5134105/01/202009/02/202012/02/202007/05/2020
6Plant6124106/01/202010/02/202013/02/202008/05/2020
7Plant7114107/01/202011/02/202014/02/202009/05/2020
8Plant8104108/01/202012/02/202015/02/202010/05/2020
9Plant994109/01/202013/02/202016/02/202011/05/2020
10Plant10125210/01/202012/01/202014/01/202016/01/2020
11
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=TODAY()-MAX(D2:GZ2)
C2:C10C2=(MAX(D2:GZ2)-D2)/(COUNTA(D2:GZ2)-1)
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top