Determining a Zodiac Sign from a Birthdate

Candice has a worksheet in which she has the 12 signs of the Zodiac (Aquarius through Capricorn). She's named this 12-cell range "Signs." Candice would like, in cell A3, for someone to enter a birth date and have the correct Zodiac sign displayed for that date in cell B3. Since the date ranges for the Zodiac signs don't correspond to actual months, Candice is having a hard time figuring out how to set up the worksheet to easily return the correct sign based on the birth date.

In setting up the worksheet, there are two columns that need to be utilized—the name of the Zodiac sign and the beginning date for that sign. It is also important to understand that you don't really need a full date for each sign because the year is not important; the beginning month and day are the same from year to year. This means that your data table could contain just an indicator of the beginning month and day for each sign. A handy way to do this is shown in this layout. (See Figure 1.)

Figure 1. Setting up Zodiac data.

Notice that the Date column consists of the month number followed by a two-digit day of the month. Also notice that Capricorn is in the table twice, since it covers dates at the beginning and end of the year. If you define the data range (D2:E14) as the "Signs" range, then you can use this formula to return a sign given any birthdate:

=VLOOKUP(MONTH(A3)*100+DAY(A3),Signs,2,TRUE)

With the birthdate in cell A3, the formula takes the month value, multiplies it by 100, and then adds the day of the month. This is then used as the VLOOKUP value into the Signs range, and the corresponding value in column 2 of that range is returned.

If you don't want to even use a data table, you can condense the entire VLOOKUP into a single formula:

=IF(ISBLANK(A3),"",VLOOKUP((MONTH(A3)*100+DAY(G2)),{0,"Capricorn";120,"Aquarius";219,"Pisces";321,"Aries";420,"Taurus";521,"Gemini";621,"Cancer";723,"Leo";823,"Virgo";923,"Libra";1023,"Scorpio";1122,"Sagittarius";1222,"Capricorn"},2,TRUE))

If you are using Excel 2007, Excel 2010, Excel 2013, Excel 2016, or Excel 2019, then you must enter this as an array formula, by using Ctrl+Shift+Enter.

If you prefer to take a macro-based result (which would also remove the need for a data table), you could create a user-defined function such as the following:

Function GetSign(d As Date) As String
    Dim sZodiac As String

    Select Case Month(d)
        Case 1
            sZodiac = "Capricorn"
            If Day(d) >= 20 Then sZodiac = "Aquarius"
        Case 2
            sZodiac = "Aquarius"
            If Day(d) >= 19 Then sZodiac = "Pisces"
        Case 3
            sZodiac = "Pisces"
            If Day(d) >= 21 Then sZodiac = "Aries"
        Case 4
            sZodiac = "Aries"
            If Day(d) >= 20 Then sZodiac = "Taurus"
        Case 5
            sZodiac = "Taurus"
            If Day(d) >= 21 Then sZodiac = "Gemini"
        Case 6
            sZodiac = "Gemini"
            If Day(d) >= 21 Then sZodiac = "Cancer"
        Case 7
            sZodiac = "Cancer"
            If Day(d) >= 23 Then sZodiac = "Leo"
        Case 8
            sZodiac = "Leo"
            If Day(d) >= 23 Then sZodiac = "Virgo"
        Case 9
            sZodiac = "Virgo"
            If Day(d) >= 23 Then sZodiac = "Libra"
        Case 10
            sZodiac = "Libra"
            If Day(d) >= 23 Then sZodiac = "Scorpio"
        Case 11
            sZodiac = "Scorpio"
            If Day(d) >= 22 Then sZodiac = "Sagittarius"
        Case 12
            sZodiac = "Sagittarius"
            If Day(d) >= 22 Then sZodiac = "Capricorn"
    End Select

    GetSign = sZodiac
End Function

To utilize the macro, in your worksheet you could enter the following:

=GetSign(A3)

Provided cell A3 contains a date, the macro will return the Zodiac sign for that date.

Finally, for additional ideas on solving Candice's problem, you can refer to the following web page:

https://exceljet.net/formula/zodiac-sign-lookup

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12908) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.