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]

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.

Apologizes for the late response (I didn't get e-mail notification).

I already have a formula for dealing with dates before March 1, 1900 by using YEAR+400. I can work with all dates (look at columns D and F above).

Cheers,

* CalvinTy

 

[CalvinTy]

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?

Thank you, xenou (sorry for late response). Our organization actually displays age in total days *as well* as days after the last birthday (i.e. 114 years 32 days). See this page as an example:

http://www.grg.org/Adams/B2.HTM

Right about defining an upper boundary and lower boundary -- that seems to be crucial in being able to output only those who were alive "between those boundaries". My data is in a spreadsheet (Fazza offered a SQL solution, but I'm over my head with SQL). I had figured a PivotTable would help me with my ultimate goal:

"To output a list of names and their ages (living ones only) based on a random date that I enter."

*ages = total days (simplest way as I can deal with Date 2 - Date 1 if needed)

* CalvinTy

 

[xenou]

A pivot table might help but you would need to start it with formulas. Perhaps with helper columns:

Column 1:
=(DateOfBirth<=RandomDate)

Column 2:
=(DateofDeath>=RandomDate) OR (DateOfDate Is Blank/Null/Empty/Not Yet)

Column 3:
=AND(Column 1, Column 2)

Column 4:
(RandomDate-DateOfBirth)


The pivot table then shows only records with TRUE in Column 3. I fear this is poorly explained and in practice you may or may not want to reduce the number of "helper" columns, or you could even use multiplication of the True/False values against the ages to coerce all non-applicable ages to zero. Below is an example of the approach - in short, just to use a helper column to decide the question of "is this a valid as of date". Then its simple math on those persons who apply as of date given.

This is an example of the concept:
<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20120530_capture.jpg" />

Formula in Cell D5:
=E5*($B$2-B5)

Formula in Cell E5:
=AND(B5<=$B$2,OR(C5>=$B$2,LEN(C5)=0))+0

<a href="http://northernocean.net/etc/mrexcel/20120530_book1.zip">Sample Workbook</a>
sha256sum: ff6b87f0666663629180ff49c047172f4a9783faa66fea1d3d5134ab2476cd5d

 

[CalvinTy]

Thanks, xenou! Let me try this and see how this goes!

We are definitely getting somewhere because even in my tab where I do an Advanced Filter, I have to put helper columns in my data (years, days, total days) of a person's age based on the same random date I entered. If the person was already deceased by that random date, I have it to output "deceased" in the helper columns.

From that, I use the Advanced Filter to use the pre-defined criteria to only look for those that are of specific age already (i.e. already over 100 years old) and *not* "deceased".

It works splendid, but for just one date. I can see in your sample in where it managed to output the 3 correct entries, all at once. Let me get to work!

Much appreciated,

* CalvinTy

 

[CalvinTy]

Thanks, xenou! Let me try this and see how this goes!

We are definitely getting somewhere because even in my tab where I do an Advanced Filter, I have to put helper columns in my data (years, days, total days) of a person's age based on the same random date I entered. If the person was already deceased by that random date, I have it to output "deceased" in the helper columns.

From that, I use the Advanced Filter to use the pre-defined criteria to only look for those that are of specific age already (i.e. already over 100 years old) and *not* "deceased".

It works splendid, but for just one date. I can see in your sample in where it managed to output the 3 correct entries, all at once. Let me get to work!

Much appreciated,

* CalvinTy

I'm so sorry, xenou. I got messed up on what I wanted to do next. As my original post said (which I forgot about!):

"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. "

I even made the wrong request in this entire thread (I already successfully did one date, oops)! Now I'm confused about what my fellow researcher was asking of me two weeks ago (about multiple dates because I now realize "is he asking for a calendar-like format where any date can be chosen" or "is he asking to show results on a daily basis"?).

Let me get back to this. I may start a new thread when I get clarification shortly.

* CalvinTy

 

[CalvinTy]

This is an example of the concept:

I just played around with your example spreadsheet, xenou, to see if I can learn more from how you set it up. When I changed the date to "1/2/2000", it correctly showed that Person4 is the only one living at that time in the table (column E showing only Person4 with "1" while everyone else is "0").

How do I (and anyone else who's interested in your excellent example) get the Pivot to show only Person4 to appear in rows 16-18? Even after I select "1" in cell E13, rows 16-18 did not update? Did I miss something?

* CalvinTy

 

[xenou]

A pivot table needs to be "refreshed" - I usually right-click in the table and then hit refresh from the short menu. There are also buttons in the menu/ribbon for refreshing data. I think when you do this the table will show only person4.

ξ

 

[CalvinTy]

A pivot table needs to be "refreshed" - I usually right-click in the table and then hit refresh from the short menu. There are also buttons in the menu/ribbon for refreshing data. I think when you do this the table will show only person4.

ξ

Yes, confirmed. I went to the PivotTable area and right-click and selected "Refresh". It now only shows Person4. Perfect. (I may end up using this type of setup rather than what I have in place).

At least if anyone doing a search on this thread, with your example, they can get the answer of "how to output only living names based on a specific date".

Thanks again for everything,

* CalvinTy