A colleague of mine came to me with a problem a few weeks ago: A student in one of his classes wanted to test a hypothesis concerning Zodiac signs (which are, of course, total crap). The problem was that he had birth dates in SPSS but didn’t want to go through individually and convert each birth date into its respective Zodiac sign (a problem when you have a couple hundred birth dates to convert). He was wondering if there was an easy way to do this in SPSS. I couldn’t think of a way to do it in SPSS, but I thought it might be possible in Excel. Since I figured they would find Zodiac signs are not predictive of anything, I was willing to spend some time putting something together (note: they did find that Zodiac signs are not predictive). The result is this spreadsheet. Basically, you put a birth date in and it registers a series of logic checks, indicating whether or not the birth date is “true” for the respective Zodiac signs. A quick scan across the columns indicates what someone’s zodiac sign is based on their birth date. I couldn’t quite figure out how to calculate a final column that returns the “true” Zodiac sign (i.e., a single column with all of the Zodiac signs), but I’m sure it’s doable. If someone out there knows a better way of doing this, I’d love to hear about it.

I’m sure any computer programmers who read this will laugh at what I’ve done, but it solved the problem.  There are websites out there that will, of course, tell you your Zodiac sign, but they only do one at a time.  If you want to convert a large list of birthdates, something like this spreadsheet will do it for you.  Again, if you have suggestions for ways to improve it or other ideas, please let me know.   Before making fun of me, keep in mind the paraphrased words of the immortal Bones from Star Trek, “Damnit, Jim, I’m a sociologist, not a computer programmer!”


Update 12/09/2008: Based on Mike’s comments and suggestions below, I created an SPSS syntax file that will basically (though still a bit clumsily) convert birthdates into Zodiac signs:


I also threw in a list of birthdays with Zodiac signs so you can check to make sure you did it right.


Again, it’s not perfect. If anyone has any suggestions or improvements, please share.

 1,458 total views,  1 views today

4 Replies to “zodiac converter”

  1. Apparently (not that I know much about this), there are personality profiles associated with each Zodiac sign. The student had each person choose 1 personality profile of the 12 he/she thought reflect their personality. The goal was to see if they chose the correct one. 186 people chose one; 17 got it right. The probability of 17 out of 186 people getting it right (using binomial probability theory) is .09, which is less than a standard alpha of .05. Ergo, 17 out of 186 getting it right is no better than by chance.

    Any thoughts on how to do the conversion in SPSS?

  2. STRING zodiacsign (A8) .
    (0321 thru 0419=’Aries’) (0420 thru 0519=’Taurus’) (0520 thru
    0620=’Gemini’) (0621 thru 0722=’Cancer’) (0723 thru 0821=’Leo’) (0822
    thru 0922=’Virgo’) (0923 thru 1022=’Libra’) (1023 thru 1121=’Scorpio’)
    (1122 thru 1221=’Saggitarius’) (1222 thru 1231=’Capricorn’) (0101 thru
    0120=’Capricorn’) (0121 thru 0219=’Aquarius’) (0220 thru 0320=’Pisces’)
    INTO zodiacsign .

    That might be someplace to start. It’s simply a matter of numerically coding each case’s birthdate as a 4-digit number (omitting the year) and recoding that into a string variable (unless you want to code into 1-12 and just have that correspond with an astrological sign). The only problem is with (us!) Capricorns, because it’s illogical to say “1220 through 0121.” So I broke it up into two separate ‘thru’ statements. It may work, I didn’t test it. But if it doesn’t, it will with a little tweaking.

    (I posted this in your wal-mart death post by accident, so you can delete that one. my bad.)

Leave a Reply

Your email address will not be published.