Determining a Zodiac Sign from a Birthdate

Written by Allen Wyatt (last updated June 4, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


4

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Table Header Rows after a Manual Page Break

Insert a manual page break into the middle of a table, and you may find that subsequent pages of the table don't always ...

Discover More

Using the SYMBOL Field

The most common way of adding symbols to a document is to use the Symbol dialog box. There is another way, however, that ...

Discover More

Using Cross-References in Footnotes

Need to make a cross-reference from one footnote to another footnote? You can do it if you throw bookmarks into the mix, ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Counting Cells with Specific Characters

Excel is used by many people to hold all sorts of data, not just numbers. If you have cells that include meaningful ...

Discover More

Pulling a Phone Number with a Known First and Last Name

When using an Excel worksheet to store data (such as names and phone numbers), you may need a way to easily look up a ...

Discover More

Locating a Single-Occurrence Value in a Column

Given a range of cells containing values, you may have a need to find the first value in the range that is unique. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 9?

2022-06-05 10:23:25

J. Woolley

My Excel Toolbox includes the following function to return the Zodiac sign corresponding to any date (numeric or text):
=ZodiacSign(AnyDate,[AsText])
If AnyDate is text, its year is unnecessary but must not exceed VBA's Date limits (0100 thru 9999). If optional AsText is FALSE (default), a Unicode character like UNICHAR(9800) is returned; otherwise, text like "Aries" is returned.
Here is an abbreviated version:

Function ZodiacSign(AnyDate As Date, Optional AsText As Boolean = False)
    Dim nDate As Integer
    Static A As Variant
    nDate = Val(Format(AnyDate, "mdd")) ' ignore year
    If IsEmpty(A) Then ' initialize
        A = Array( _
            Array(101, ChrW(9809), "Capricorn"), _
            Array(120, ChrW(9810), "Aquarius"), _
            Array(219, ChrW(9811), "Pisces"), _
            Array(321, ChrW(9800), "Aries"), _
            Array(420, ChrW(9801), "Taurus"), _
            Array(521, ChrW(9802), "Gemini"), _
            Array(621, ChrW(9803), "Cancer"), _
            Array(723, ChrW(9804), "Leo"), _
            Array(823, ChrW(9805), "Virgo"), _
            Array(923, ChrW(9806), "Libra"), _
            Array(1023, ChrW(9807), "Scorpio"), _
            Array(1122, ChrW(9808), "Sagittarius"), _
            Array(1222, ChrW(9809), "Capricorn") _
            )
    End If
    ZodiacSign = WorksheetFunction.VLookup(nDate, A, IIf(AsText, 3, 2))
End Function

Notice A is a static (constant) array of 13 arrays, each with 3 elements:
A(0 to 12)(0 to 2)
Because of the special relation between a VBA array and the Excel Range object, the VLookup method treats A as a range (table_array) with 13 rows and 3 columns.
Also notice VBA’s ChrW function is similar to Excel’s UNICHAR function.
See https://sites.google.com/view/MyExcelToolbox/


2022-06-05 10:20:47

J. Woolley

If text "Aries" thru "Pisces" are replaced with integers 9800 thru 9811, a formula like this will return the Unicode Zodiac character:
=UNICHAR(VLOOKUP(...))


2022-06-05 07:02:10

Joël Courtheyn

I find the use of a single formula not using a data table a very nice concept.

In my Excel application (office 365 - Dutch language) I had to use the following formula
=ALS(ISLEEG(A3);"";VERT.ZOEKEN((MAAND(A3)*100+DAG(A3));{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;WAAR))

i'm not specially referring to the typo : Day(G3) which should obviously be Day(A3) as mentioned in the comment from J. Woolley below.
It concerns the specification of the 13 rows * 2 columns array constant to be used instead of the usual tabel.
I had to separate the number from the corresponding zodiac-sign with a backslash (\) instead of a comma (,).
And so it nicely worked.


2022-06-04 10:49:39

J. Woolley

Re. the paragraph that begins "If you don't want to even use a data table, you can condense the entire VLOOKUP into a single formula." That formula includes an array constant. Because there is no manipulation of the array, such as multiplication, I don't believe it is necessary to enter the formula "as an array formula, by using Ctrl+Shift+Enter." I can't test this, however, since I'm using Excel 365.
Also, there is a typo in that formula: DAY(G2) should be changed to DAY(A3).
Also, it seems superfluous to test for blank since the previous formula and the UDF both require a valid date.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.