About the horoscope and dates of birth
- Apr. 06, 2014
 | Example files with this article: |
Introduction
I was playing around with Excel’s Table functionality (very handy!) while inputing the dates of birth and ages of my fellow soccer team members. Combined with reading up a bit on the modern horoscope (here) I thought: let’s set up some simple formulas to add the horoscope next to the dates of birth. This article will show and explain the formulas to you, and the attachment at the top of the page allows you to follow along in your favourite spreadsheet program.
-
Immediately above you can see a Table containing 6 columns:
- column A: input for the name of the person
- column B: input for the date of birth
- column C: a formula to present the date of birth as a monthname. Here it shows in Dutch but I bet you will understand :-)
- column D: a formula to retrieve the horoscope name from a different table
- column E: a formula to calculate the age of the person as of today
- column F: a formula to know whether the person already got his/her annyversary for this year
Explaining the formulas
Column C's formula is as follows:
=TEXT([@[Date of birth]],"mmmm")
The TEXT function converts the date to a textual representation, here "mmmm" for the full month name (following the regional and language settings on the PC). The part of "[@[Date of birth]]" instructs Excel to fetch the Date of birth for that person (row by row). This notation will hold in all rows of the table: when you change the formula only in 1 cell of the column, Excel will automatically fill down (and up) that formula to all cells in the column. No need to copy/paste any more, you always end up with the correct number of rows since tables are dynamic.
Column D's formula is as follows:
=LOOKUP(TEXT([@[Date of birth]],"mm\_dd"),tbl_Horoscope[BeginDate],tbl_Horoscope[Horoscope])
This is again a formula that uses relative referencing in a Table. The Date of birth (converted to mm_dd notation) is looked up in a different table called tbl_Horoscope:
For John, who happens to be born on January 25, 1976, we will look up 01_25 in the column called "BeginDate" in the table called "tbl_Horoscope". Hence the notation in the formula: tbl_Horoscope[BeginDate]. Only a dozen rows are in that table: intermediate dates will be looked up in an approximate way. Note how I split up the horoscope for the Capricorn, since it crosses years during the period it is active. Also note that in the TEXT function the \ character escapes the _ character that follows.
For John, the result is that the value 01_20 matches his 01_25. The LOOKUP function then grabs the value for the Horoscope field in that table and returns: Aries. Syntaxis: tbl_Horoscope[Horoscope]. Do you understand this mechanism of linking tables? Instead of the LOOKUP formula, the good old VLOOKUP formula is possible too. But here in this case, I find the formula easier to follow, and we avoid typing numbers for columns in the VLOOKUP. In addition, you can now move around columns in the table tbl_Horoscope, the formula will not be broken.
Column E's formula is as follows:
=DATEDIF([@[Date of birth]],TODAY(),"y")
This is the typical Excel formula to calculate the number of entire years that has passed since a given date: we want the time interval in years in between Date of birth and today’s date. Hence, the current age of a person.
Column F's formula is as follows:
=IF( TEXT( [@[Date of birth]];"mm\_dd") <= TEXT( TODAY();"mm\_dd");"Yes";"No")
This formula should now be obvious to you: it calculates whether the person could already celebrate his/her anniversary during the current year, starting January 1. A test on months and days is wrapped in an IF formula, the output is "Yes" or "No".
Conditional formatting
It might not be easily visible on the first screenshot, but I added conditional formatting to the Age field: those persons who can still celebrate their anniversary this year, will be presented in bold. Mary, Peter and Amy are the lucky ones. This conditional formatting is trivial for the readers of my Excel articles :-)
Exercise
I leave it up to you to do the following exercise. Assume the Date of birth is unknown to you. Instead of inputing the Dates of birth, input the horoscope names, like Taurus, Leo, Libra, Virgo, and so on. Based on that information and a lookup table, determine the period that the person can enjoy his/her anniversary. For instance, for Virgo, the formula should give you: Aug, 23 - Sep, 22. Good luck!
End of story
Right, there we are, an easy article to show some good usage of Excel tables combined with the Horoscope and Dates of birth. Happy anniversary!
