How best to output names/dates based on a specific date?

[CalvinTy]

I have been helping the organization I volunteer my Excel knowledge (which is still average). We work with data such as lifespans of oldest people all over the world (for instance, I created this output file for my organization that was posted on this page by the organization).

In the last few months, I have worked with conditional formatting, dealing with Year+400 (dates before 1900 with the workaround of adding 400 years to the Gregorian calendar), and using SUMPRODUCT to determine highest rank of a person.

Now, a couple of months ago, per request, I was able to determine precisely how many names were living on a particular date via multiple SUMPRODUCT arrays (first array was whether the death date was later than desired date; second array was whether that case was at least 110 years of age; 3rd array was whether that case's status was verified and/or living). Of course, I did this the long way by entering each date in each row from 1955 to present (I know, crazy of me, huh?).

More recently, I began to use PivotTable and was able to provide reports of how many cases died in a specific year. I knew that PivotTable probably has what I'm looking for now but I'm tinkering without luck:

I'm now looking to create/setup a Pivot Table where I enter a random date and it would only show me who was living on that date out of all names.

I already had a separate Tab in where I enter a day, month, year in three cells (which actually goes to yet another tab in where I calculated their ages as of that date if still living). Back to the "AsOfDate" tab, I was able to set up an Advanced Filter to pick a list range (master table), criteria range (criteria in another tab where I asked only for living cases & at least age of 110), and copying to the headers of the current tab.

It works beautifully in telling us who was living on that date. However, it means that I have to enter a new date to re-do the Advanced Filter.

Instead, I think PivotTable has the answer but I don't see how I can tell PivotTable a specific date (the only relevant date formats I have in the master data are obviously the born+400 dates and died+400 dates). From what I'm seeing, I can only choose dates that are in the data? Or am I missing something obvious?

How would you go around making a living list of cases as of a specific date? For example, to do a smaller dataset, like the list of United States presidents by age, how would I know how many U.S. presidents were alive on January 1, 1955?

P.S. Sorry for the long post. I'm a chatterbox.

* CalvinTy

 

[CalvinTy]

How would you go around making a living list of cases as of a specific date? For example, to do a smaller dataset, like the list of United States presidents by age, how would I know how many U.S. presidents were alive on January 1, 1955?

I have created a sample Excel spreadsheet based on United States presidents by age, but I guess I'm not able to post attachments (yet).

At work, I'm not able to use file services such as Google Docs or Microsoft Skydrive. Let me learn how to use Excel Jeanie... okay, I was able to use Excel Jeanie but it warned me that I had more characters than what a forum would allow (makes sense) so I went ahead and removed all formulas from rows 3 downwards.

Excel Jeanie also did not handle background formatting well, apparently.

Presidents Tab (Today's date in cell $L$2 in text format; today's date+400 in cell $M$2 in date format)
(Conditional Formatting: =$E2="" then format range =$A$2:$I:$44 with colored light green fill)

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:26px;"><col style="width:153px;"><col style="width:71px;"><col style="width:76px;"><col style="width:71px;"><col style="width:74px;"><col style="width:59px;"><col style="width:59px;"><col style="width:70px;"></colgroup><tbody><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><td>F</td><td>G</td><td>H</td><td>I</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">#</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">President</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Born</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Born+400</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Died</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Died+400</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Years</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Days</td><td style="font-weight:bold; text-decoration:underline; text-align:center; ">Total Days</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">1</td><td>George Washington</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Feb-1732</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Feb-2132</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Dec-1799</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Dec-2199</td><td style="text-align:center; ">67</td><td style="text-align:center; ">295</td><td style="text-align:center; ">24767</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">2</td><td>John Adams</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">30-Oct-1735</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">30-Oct-2135</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-1826</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-2226</td><td style="text-align:center; ">90</td><td style="text-align:center; ">247</td><td style="text-align:center; ">33119</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">3</td><td>Thomas Jefferson</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">13-Apr-1743</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">13-Apr-2143</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-1826</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-2226</td><td style="text-align:center; ">83</td><td style="text-align:center; ">82</td><td style="text-align:center; ">30397</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">4</td><td>James Madison</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">16-Mar-1751</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">16-Mar-2151</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Jun-1836</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Jun-2236</td><td style="text-align:center; ">85</td><td style="text-align:center; ">104</td><td style="text-align:center; ">31150</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">5</td><td>James Monroe</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Apr-1758</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Apr-2158</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-1831</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-2231</td><td style="text-align:center; ">73</td><td style="text-align:center; ">67</td><td style="text-align:center; ">26729</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; ">6</td><td>John Quincy Adams</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">11-Jul-1767</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">11-Jul-2167</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Feb-1848</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Feb-2248</td><td style="text-align:center; ">80</td><td style="text-align:center; ">227</td><td style="text-align:center; ">29446</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; ">7</td><td>Andrew Jackson</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Mar-1767</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Mar-2167</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Jun-1845</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Jun-2245</td><td style="text-align:center; ">78</td><td style="text-align:center; ">85</td><td style="text-align:center; ">28574</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; ">8</td><td>Martin Van Buren</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Dec-1782</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Dec-2182</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">24-Jul-1862</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">24-Jul-2262</td><td style="text-align:center; ">79</td><td style="text-align:center; ">231</td><td style="text-align:center; ">29085</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:center; ">9</td><td>William Henry Harrison</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">9-Feb-1773</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">9-Feb-2173</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Apr-1841</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Apr-2241</td><td style="text-align:center; ">68</td><td style="text-align:center; ">54</td><td style="text-align:center; ">24890</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:center; ">10</td><td>John Tyler</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-Mar-1790</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-Mar-2190</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">18-Jan-1862</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">18-Jan-2262</td><td style="text-align:center; ">71</td><td style="text-align:center; ">295</td><td style="text-align:center; ">26227</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:center; ">11</td><td>James K. Polk</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">2-Nov-1795</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">2-Nov-2195</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Jun-1849</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Jun-2249</td><td style="text-align:center; ">53</td><td style="text-align:center; ">225</td><td style="text-align:center; ">19583</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:center; ">12</td><td>Zachary Taylor</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">24-Nov-1784</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">24-Nov-2184</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">9-Jul-1850</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">9-Jul-2250</td><td style="text-align:center; ">65</td><td style="text-align:center; ">227</td><td style="text-align:center; ">23967</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:center; ">13</td><td>Millard Fillmore</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">7-Jan-1800</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">7-Jan-2200</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Mar-1874</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Mar-2274</td><td style="text-align:center; ">74</td><td style="text-align:center; ">60</td><td style="text-align:center; ">27088</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:center; ">14</td><td>Franklin Pierce</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Nov-1804</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Nov-2204</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Oct-1869</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Oct-2269</td><td style="text-align:center; ">64</td><td style="text-align:center; ">319</td><td style="text-align:center; ">23695</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="text-align:center; ">15</td><td>James Buchanan</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Apr-1791</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Apr-2191</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">1-Jun-1868</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">1-Jun-2268</td><td style="text-align:center; ">77</td><td style="text-align:center; ">39</td><td style="text-align:center; ">28163</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:center; ">16</td><td>Abraham Lincoln</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">12-Feb-1809</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">12-Feb-2209</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Apr-1865</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Apr-2265</td><td style="text-align:center; ">56</td><td style="text-align:center; ">62</td><td style="text-align:center; ">20516</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:center; ">17</td><td>Andrew Johnson</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-Dec-1808</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-Dec-2208</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">31-Jul-1875</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">31-Jul-2275</td><td style="text-align:center; ">66</td><td style="text-align:center; ">214</td><td style="text-align:center; ">24320</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="text-align:center; ">18</td><td>Ulysses S. Grant</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">27-Apr-1822</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">27-Apr-2222</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Jul-1885</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">23-Jul-2285</td><td style="text-align:center; ">63</td><td style="text-align:center; ">87</td><td style="text-align:center; ">23098</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="text-align:center; ">19</td><td>Rutherford B. Hayes</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Oct-1822</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Oct-2222</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">17-Jan-1893</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">17-Jan-2293</td><td style="text-align:center; ">70</td><td style="text-align:center; ">105</td><td style="text-align:center; ">25673</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td style="text-align:center; ">20</td><td>James A. Garfield</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">19-Nov-1831</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">19-Nov-2231</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">19-Sep-1881</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">19-Sep-2281</td><td style="text-align:center; ">49</td><td style="text-align:center; ">304</td><td style="text-align:center; ">18202</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td style="text-align:center; ">21</td><td>Chester A. Arthur</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Oct-1829</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Oct-2229</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">18-Nov-1886</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">18-Nov-2286</td><td style="text-align:center; ">57</td><td style="text-align:center; ">44</td><td style="text-align:center; ">20863</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td style="text-align:center; ">22</td><td>Grover Cleveland</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">18-Mar-1837</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">18-Mar-2237</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">24-Jun-1908</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">24-Jun-2308</td><td style="text-align:center; ">71</td><td style="text-align:center; ">98</td><td style="text-align:center; ">26030</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style="text-align:center; ">23</td><td>Benjamin Harrison</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">20-Aug-1833</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">20-Aug-2233</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">13-Mar-1901</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">13-Mar-2301</td><td style="text-align:center; ">67</td><td style="text-align:center; ">205</td><td style="text-align:center; ">24676</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style="text-align:center; ">24</td><td>William McKinley</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-Jan-1843</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-Jan-2243</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Sep-1901</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Sep-2301</td><td style="text-align:center; ">58</td><td style="text-align:center; ">228</td><td style="text-align:center; ">21412</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style="text-align:center; ">25</td><td>Theodore Roosevelt</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">27-Oct-1858</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">27-Oct-2258</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">6-Jan-1919</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">6-Jan-2319</td><td style="text-align:center; ">60</td><td style="text-align:center; ">71</td><td style="text-align:center; ">21985</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td style="text-align:center; ">26</td><td>William Howard Taft</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Sep-1857</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">15-Sep-2257</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Mar-1930</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-Mar-2330</td><td style="text-align:center; ">72</td><td style="text-align:center; ">174</td><td style="text-align:center; ">26471</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td style="text-align:center; ">27</td><td>Woodrow Wilson</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Dec-1856</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Dec-2256</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">3-Feb-1924</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">3-Feb-2324</td><td style="text-align:center; ">67</td><td style="text-align:center; ">37</td><td style="text-align:center; ">24507</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td style="text-align:center; ">28</td><td>Warren G. Harding</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">2-Nov-1865</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">2-Nov-2265</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">2-Aug-1923</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">2-Aug-2323</td><td style="text-align:center; ">57</td><td style="text-align:center; ">273</td><td style="text-align:center; ">21091</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td style="text-align:center; ">29</td><td>Calvin Coolidge</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-1872</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Jul-2272</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Jan-1933</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Jan-2333</td><td style="text-align:center; ">60</td><td style="text-align:center; ">185</td><td style="text-align:center; ">22099</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td style="text-align:center; ">30</td><td>Herbert Hoover</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">10-Aug-1874</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">10-Aug-2274</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">20-Oct-1964</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">20-Oct-2364</td><td style="text-align:center; ">90</td><td style="text-align:center; ">71</td><td style="text-align:center; ">32943</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">32</td><td style="text-align:center; ">31</td><td>Franklin D. Roosevelt</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">30-Jan-1882</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">30-Jan-2282</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">12-Apr-1945</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">12-Apr-2345</td><td style="text-align:center; ">63</td><td style="text-align:center; ">72</td><td style="text-align:center; ">23082</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">33</td><td style="text-align:center; ">32</td><td>Harry S. Truman</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-May-1884</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">8-May-2284</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">26-Dec-1972</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">26-Dec-2372</td><td style="text-align:center; ">88</td><td style="text-align:center; ">232</td><td style="text-align:center; ">32373</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">34</td><td style="text-align:center; ">33</td><td>Dwight D. Eisenhower</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Oct-1890</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Oct-2290</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Mar-1969</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">28-Mar-2369</td><td style="text-align:center; ">78</td><td style="text-align:center; ">165</td><td style="text-align:center; ">28654</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">35</td><td style="text-align:center; ">34</td><td>John F. Kennedy</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-May-1917</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">29-May-2317</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Nov-1963</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Nov-2363</td><td style="text-align:center; ">46</td><td style="text-align:center; ">177</td><td style="text-align:center; ">16978</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">36</td><td style="text-align:center; ">35</td><td>Lyndon B. Johnson</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">27-Aug-1908</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">27-Aug-2308</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Jan-1973</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Jan-2373</td><td style="text-align:center; ">64</td><td style="text-align:center; ">148</td><td style="text-align:center; ">23524</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">37</td><td style="text-align:center; ">36</td><td>Richard Nixon</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">9-Jan-1913</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">9-Jan-2313</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Apr-1994</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">22-Apr-2394</td><td style="text-align:center; ">81</td><td style="text-align:center; ">103</td><td style="text-align:center; ">29688</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">38</td><td style="text-align:center; ">37</td><td>Gerald Ford</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Jul-1913</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">14-Jul-2313</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">26-Dec-2006</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">26-Dec-2406</td><td style="text-align:center; ">93</td><td style="text-align:center; ">165</td><td style="text-align:center; ">34133</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">39</td><td style="background-color:#92d050; text-align:center; ">38</td><td>Jimmy Carter</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">1-Oct-1924</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">1-Oct-2324</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="background-color:#92d050; text-align:center; ">87</td><td style="background-color:#92d050; text-align:center; ">226</td><td style="background-color:#92d050; text-align:center; ">32002</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">40</td><td style="text-align:center; ">39</td><td>Ronald Reagan</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">6-Feb-1911</td><td style="color:#003366; background-color:#d99795; font-family:Times New Roman; font-size:10pt; text-align:right; ">6-Feb-2311</td><td style="color:#003366; background-color:#ddd9c3; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Jun-2004</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">5-Jun-2404</td><td style="text-align:center; ">93</td><td style="text-align:center; ">120</td><td style="text-align:center; ">34088</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">41</td><td style="text-align:center; ">40</td><td>George H. W. Bush</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">12-Jun-1924</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">12-Jun-2324</td><td style="font-family:Times New Roman; font-size:10pt; ">
</td><td style="font-family:Times New Roman; font-size:10pt; ">
</td><td style="text-align:center; ">87</td><td style="text-align:center; ">337</td><td style="background-color:#92d050; text-align:center; ">32113</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">42</td><td style="text-align:center; ">41</td><td>Bill Clinton</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">19-Aug-1946</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">19-Aug-2346</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="text-align:center; ">65</td><td style="background-color:#92d050; text-align:center; ">269</td><td style="text-align:center; ">24010</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">43</td><td style="background-color:#92d050; text-align:center; ">42</td><td style="background-color:#92d050; ">George W. Bush</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">6-Jul-1946</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">6-Jul-2346</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="font-family:Times New Roman; font-size:10pt; ">
</td><td style="background-color:#92d050; text-align:center; ">65</td><td style="text-align:center; ">313</td><td style="text-align:center; ">24054</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">44</td><td style="background-color:#92d050; text-align:center; ">43</td><td>Barack Obama</td><td style="color:#003366; background-color:#92d050; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Aug-1961</td><td style="color:#003366; font-family:Times New Roman; font-size:10pt; text-align:right; ">4-Aug-2361</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="background-color:#92d050; font-family:Times New Roman; font-size:10pt; ">
</td><td style="background-color:#92d050; text-align:center; ">50</td><td style="background-color:#92d050; text-align:center; ">284</td><td style="background-color:#92d050; text-align:center; ">18546</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>D2</td><td>=IF(C2="","",DATE(RIGHT(C2,4)+400,VLOOKUP(LEFT(RIGHT(C2,8),3),Month!$A$4:$B$15,2,FALSE),LEFT(C2,FIND("-",C2,1)-1)))</td></tr><tr><td>F2</td><td>=IF(E2="","",DATE(RIGHT(E2,4)+400,VLOOKUP(LEFT(RIGHT(E2,8),3),Month!$A$4:$B$15,2,FALSE),LEFT(E2,FIND("-",E2,1)-1)))</td></tr><tr><td>G2</td><td>=IF(F2="",(YEAR($M$2)-YEAR(D2)-IF((MONTH($M$2)*31+DAY($M$2))>=(MONTH(D2)*31+DAY(D2)),0,1)),(YEAR(F2)-YEAR(D2)-IF((MONTH(F2)*31+DAY(F2))>=(MONTH(D2)*31+DAY(D2)),0,1)))</td></tr><tr><td>H2</td><td>=IF(F2="",($M$2-DATE(YEAR(D2)+G2,MONTH(D2),IF(AND(DAY(D2)=29,MONTH(D2)=2,DAY(DATE(YEAR(D2)+G2,MONTH(D2),DAY(D2)))<>29),28,DAY(D2)))),(F2-DATE(YEAR(D2)+G2,MONTH(D2),IF(AND(DAY(D2)=29,MONTH(D2)=2,DAY(DATE(YEAR(D2)+G2,MONTH(D2),DAY(D2)))<>29),28,DAY(D2)))))</td></tr><tr><td>I2</td><td>=IF(F2="",$M$2-D2,F2-D2)</td></tr></tbody></table></td></tr></tbody></table>
Month Tab (usually hidden in my files with our dataset)

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:48px;"><col style="width:39px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; ">Month Table</td><td style="font-weight:bold; ">
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-weight:bold; ">
</td><td style="font-weight:bold; ">
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; ">
</td><td style="font-family:Arial; font-size:10pt; ">
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">Jan</td><td style="text-align:center; ">1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">Feb</td><td style="text-align:center; ">2</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">Mar</td><td style="text-align:center; ">3</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; ">Apr</td><td style="text-align:center; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; ">May</td><td style="text-align:center; ">5</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; ">Jun</td><td style="text-align:center; ">6</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:center; ">Jul</td><td style="text-align:center; ">7</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:center; ">Aug</td><td style="text-align:center; ">8</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:center; ">Sep</td><td style="text-align:center; ">9</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:center; ">Oct</td><td style="text-align:center; ">10</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:center; ">Nov</td><td style="text-align:center; ">11</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:center; ">Dec</td><td style="text-align:center; ">12</td></tr></tbody></table>
Okay, from this post, how would you list all U.S. presidents that were alive on January 1, 1955 as an example?

Also, since I used the formulas in basically the same fashion with my larger dataset, if someone sees that I could calculate a specific cell in a better way, please let me know!

Thanks for reading,

* CalvinTy

 

[Fazza]

hi. Suggest you make it easier for those interested in answering the question by posting simple sample data.

It sounds to me as if there is a list of data and another entry that will filter that. For sure I've posted plenty of previous times on these sorts of questions. I suspect a query table will give as good as a result as a pivot table & they are similar in approach. Solution is to do the filtering in SQL [& feed that dataset into the table]. If using a pivot table, easier to give the source data normal defined names [not dynamic], save the file, open a new file and choose the external data option at the first step of the wizard - so ALT-D-P to start, then follow the wizard. SQL such as below.

HTH

SELECT BT.fields_you_want
FROM big_data_table BT, table_with_filter_entrie/s ST
WHERE BT.common_field = ST.common_field

 

[Fazza]

PS. I see you have now posted some sample data. Great. Thanks

 

[CalvinTy]

PS. I see you have now posted some sample data. Great. Thanks

No problem. Just saw your posts. Also, to clarify, part of the dilemma I am having is that once I get the desired list of names (i.e. who were alive on a specific date), for our dataset, we do need to display how old they were on that specific date, too.

My skills in any kind of programming is very basic at best (I can follow logical comments but it'll take me time... I'll see what filtering in SQL is all about).

Cheers,

* CalvinTy

 

[CalvinTy]

Just want to see if anyone has an idea on how to output a list of names and their ages (living ones only) based on a random date that I enter.

I am hoping that the list of the U.S. presidents above would help someone provide possible solutions -- so I can apply it to my larger dataset.

Thanks in advance,

* CalvinTy

 

[xenou]

Presumably if you had the birthdates of everyone in the world, and you filtered for any birthdate, then you would be able to show all people with that birthdate in your pivot table. Beyond that, you only need up to the minute information on who is yet living or now deceased. If it is only a matter of US Presidents then the dataset is much simplified.

ξ

 

[CalvinTy]

Presumably if you had the birthdates of everyone in the world, and you filtered for any birthdate, then you would be able to show all people with that birthdate in your pivot table.

xenou, thank you for your reply. Actually, that is not what I'm looking for. In fact, if I just wanted to find an exact birthdate in a large list, I would just use "AutoFilter", and in the column with the date format, I'd choose only that birthdate to show who have had that birth date.

Rather, I'm interested in a list of names that were living on any specific date, such as January 1, 1955. For the U.S. presidents, that should output something like this:

Herbert Hoover
Harry S. Truman
Dwight D. Eisenhower
John F. Kennedy
Lyndon B. Johnson
Richard Nixon
Gerald Ford
Jimmy Carter
Ronald Reagan
George H. W. Bush
Bill Clinton
George W. Bush

...because they were all alive on January 1, 1955 (it would exclude FDR and exclude Obama).

I also want to take one step further, and display their actual age as of that date based on their birth date.

Can this be done?

* CalvinTy

 

[Aladin Akyurek]

xenou, thank you for your reply. Actually, that is not what I'm looking for. In fact, if I just wanted to find an exact birthdate in a large list, I would just use "AutoFilter", and in the column with the date format, I'd choose only that birthdate to show who have had that birth date.

Rather, I'm interested in a list of names that were living on any specific date, such as January 1, 1955. For the U.S. presidents, that should output something like this:

Herbert Hoover
Harry S. Truman
Dwight D. Eisenhower
John F. Kennedy
Lyndon B. Johnson
Richard Nixon
Gerald Ford
Jimmy Carter
Ronald Reagan
George H. W. Bush
Bill Clinton
George W. Bush

...because they were all alive on January 1, 1955 (it would exclude FDR and exclude Obama).

I also want to take one step further, and display their actual age as of that date based on their birth date.

Can this be done?

* CalvinTy

You need to calculate with dates prior to 1900. I believe Walkenbach has an add-in with appropriate functions, though not sure whether they work on Excel 2007 and later.

 

[xenou]

You have to define what you mean by age. That can be answered in years (integer), years (decimal), days. Other ways of counting and reporting age also exist. The "everyday" measure is that your age is counted in years that increment on your birthday. Is that what you want? A simpler answer, which would be accurate as a close estimate is [(Date2-Date1)/365.25] (this is measured in years). A more accurate answer would be just Date2-Date1 (this is number of days). You would need to factor in the possibility that the person has died, which is an upper boundary on the age, or that they are not yet born, which is a lower boundary.

Edit: your post confuses me because you talk about SQL but say you want a pivot table. Are you trying to write a query or create a pivot table? Is your data in a database or in a spreadsheet?