Trying to redeem myself from being an idiot!

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
121
I apologize, I really got reamed for my last post (and justifiably so) and I will try to make it right this time as not to waste your time again. I would like to get the number of days between the last two dates in the date column that has the task columns checked (columns C, D, E & etc.). There will be no blank cells in the Date column (A6:A41) as far as it goes. Example: Days between last two yard services would be 45 days.. Date 4/22/19 is in cell A6 and date 9/5/19 is in cell A29. At the very least I need the row number of the last and next to last checked cells in any one column. And in formula form please, I am willing to use helper cells if necessary. And there are many more task columns that I didn't list here. Hope this is enough information. Thank you again for all the help in the past and a big thank you for any help you may provide now.

DateDayMowWeed EatYard ServiceSeeded
04/22/19Monday✔
05/14/19Tuesday
05/20/19Monday✔✔
05/21/19Tuesday
05/30/19Thursday✔
06/10/19Monday✔
06/11/19Tuesday✔
06/17/19Monday✔✔
06/24/19Monday✔✔
07/09/19Tuesday✔✔
07/10/19Wednesday
07/17/19Wednesday✔✔
07/22/19Monday
07/23/19Tuesday✔✔
07/26/19Friday✔
07/27/19Saturday✔
08/01/19Thursday✔✔
08/06/19Tuesday✔
08/08/19Thursday✔✔
08/15/19Thursday✔✔
08/22/19Thursday✔✔
08/29/19Thursday✔✔
09/02/19Monday✔
09/05/19Thursday✔✔
Days From Today0 days0 days41 days3 days
Last Day Task Performed09/05/1909/05/1907/26/1909/02/19
Days Between Last Two Tasks

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,742
Something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;background-color: #FAFAFA;;">Date</td><td style="color: #333333;background-color: #FAFAFA;;">Day</td><td style="color: #333333;background-color: #FAFAFA;;">Mow</td><td style="color: #333333;background-color: #FAFAFA;;">Weed Eat</td><td style="color: #333333;background-color: #FAFAFA;;">Yard Service</td><td style="color: #333333;background-color: #FAFAFA;;">Seeded</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">4/22/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">5/14/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">5/20/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">5/21/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">5/30/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6/10/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6/11/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6/17/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6/24/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/9/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/10/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Wednesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/17/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Wednesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/22/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/23/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/26/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Friday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/27/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Saturday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8/1/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8/6/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Tuesday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8/8/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8/15/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8/22/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8/29/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">9/2/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Monday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">9/5/2019</td><td style="color: #333333;background-color: #FAFAFA;;">Thursday</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td><td style="color: #333333;background-color: #FAFAFA;;">?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="color: #333333;background-color: #FAFAFA;;">Days From Today</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">0 days</td><td style="color: #333333;background-color: #FAFAFA;;">0 days</td><td style="color: #333333;background-color: #FAFAFA;;">41 days</td><td style="color: #333333;background-color: #FAFAFA;;">3 days</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style="color: #333333;background-color: #FAFAFA;;">Last Day Task Performed</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">9/5/2019</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">9/5/2019</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7/26/2019</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">9/2/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style="color: #333333;background-color: #FAFAFA;;">Days Between Last Two Tasks</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">45</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">27</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C36</th><td style="text-align:left">=INDEX(<font color="Blue">$A:$A,AGGREGATE(<font color="Red">14,6,ROW(<font color="Green">C6:C31</font>)/(<font color="Green">C6:C31="✔"</font>),1</font>)</font>)-INDEX(<font color="Blue">$A:$A,AGGREGATE(<font color="Red">14,6,ROW(<font color="Green">C6:C31</font>)/(<font color="Green">C6:C31="✔"</font>),2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

You should also specify how you are creating the check marks. Is it by using a Wingdings type font? A UNICODE value? An actual check box? If so, is that linked to a cell we can reference for the TRUE/FALSE value? As it is, I just used the symbol in the formula, but that's easily changed.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,742
Also note that the HTML tool I used did not copy over the checks and boxes, but they should be the same as in your example.
 

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
121
Thank you so much Eric. I have tried ever formula (at least 3 dozen different ones that was suppose to work) that could find on google and none worked. Yours worked perfectly except for when there is only one entry in the task column but that is to be expected. Many thanks again. You are the man.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,104
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top