Summerizing Dates

[clayt101]

I have a column of both future and past dates. I would like to create another column that groups the dates into summary classes (ie. Overdue, Due in 7 Days, Due Between 8 - 30 Days, Due Between 31 - 90 Days, Due 91 - 365 Days). Is there a formula that can do this?

 

[Peter_SSs]

It isn't clear how you want the results set out, but see if something like this could be of use.
Each formula in column E is copied down.

<b>Date Groups</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:101px;" /><col style="width:28px;" /><col style="width:135px;" /><col style="width:84px;" /><col style="width:145px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">Dates</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Today: </td><td style="font-size:10pt; text-align:right; ">6/11/2019</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">6/12/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Overdue</td><td style="font-size:10pt; text-align:right; ">0</td><td style="font-size:10pt; text-align:right; ">1/01/2018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">1/12/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">30/03/2018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">9/10/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">26/06/2018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">11/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">17/03/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">3/03/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">27/07/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">26/06/2018</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">19/01/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">17/03/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Due in 7 days</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; text-align:right; ">10/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">1/01/2018</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">11/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">15/01/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">11/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">11/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">30/03/2018</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">5/01/2021</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Due in 8-30 days</td><td style="font-size:10pt; text-align:right; ">30</td><td style="font-size:10pt; text-align:right; ">25/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">10/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1/12/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">25/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">18/02/2021</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">30/05/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Due in 31-90 days</td><td style="font-size:10pt; text-align:right; ">90</td><td style="font-size:10pt; text-align:right; ">6/12/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">27/07/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">15/01/2020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">4/07/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">19/01/2020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Due in 91-365 days</td><td style="font-size:10pt; text-align:right; ">365</td><td style="font-size:10pt; text-align:right; ">3/03/2020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">30/05/2020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4/07/2020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">9/10/2020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >=TODAY()</td></tr><tr><td >E2</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,$A$2:$A$20/<span style=' color:#0000ff; '>($A$2:$A$20-D$1<$D$2)</span>,ROWS<span style=' color:#0000ff; '>(E$2:E2)</span>)</span>,"")</td></tr><tr><td >E9</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,$A$2:$A$20/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>($A$2:$A$20-D$1<$D$9)</span>*<span style=' color:#ff0000; '>($A$2:$A$20-D$1>=D$2)</span>)</span>,ROWS<span style=' color:#0000ff; '>(E$9:E9)</span>)</span>,"")</td></tr><tr><td >E14</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,$A$2:$A$20/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>($A$2:$A$20-D$1<$D$14)</span>*<span style=' color:#ff0000; '>($A$2:$A$20-D$1>=D$9)</span>)</span>,ROWS<span style=' color:#0000ff; '>(E$14:E14)</span>)</span>,"")</td></tr><tr><td >E18</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,$A$2:$A$20/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>($A$2:$A$20-D$1<$D$18)</span>*<span style=' color:#ff0000; '>($A$2:$A$20-D$1>=D$14)</span>)</span>,ROWS<span style=' color:#0000ff; '>(E$18:E18)</span>)</span>,"")</td></tr><tr><td >E23</td><td >=IFERROR(AGGREGATE<span style=' color:008000; '>(15,6,$A$2:$A$20/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>($A$2:$A$20-D$1<$D$23)</span>*<span style=' color:#ff0000; '>($A$2:$A$20-D$1>=D$18)</span>)</span>,ROWS<span style=' color:#0000ff; '>(E$23:E23)</span>)</span>,"")</td></tr></table></td></tr></table>

 

[clayt101]

Wow, that is some serious coding! I was trying to do it with if / then statements! I will try this tomorrow at the office. Thanks