Trying to redeem myself from being an idiot!

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
127
Office Version
  1. 2011
Platform
  1. MacOS
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,746
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
10,746
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
127
Office Version
  1. 2011
Platform
  1. MacOS
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,127,183
Messages
5,623,242
Members
415,957
Latest member
Newguy1924

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