Excel Short & Sweet Tip #8 (VBA Function to Return Zodiac Sign) by David Hager

Here is a VBA function procedure that will allow you to return the sign of the zodiac for a given data.

Copy/paste it into a general module in the VBE.

Public Function ZodiacSign(BirthDate As Date) As String

Dim iDayofYear As Integer

iDayofYear = DateDiff(“d”, CDate(“1/1/” & Year(BirthDate)), BirthDate) + 1

If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then

iDayofYear = iDayofYear – 1

End If

If iDayofYear < 20 Then

ZodiacSign = “Capricorn”

ElseIf iDayofYear < 50 Then

ZodiacSign = “Aquarius”

ElseIf iDayofYear < 81 Then

ZodiacSign = “Pisces”

ElseIf iDayofYear < 111 Then

ZodiacSign = “Aries”

ElseIf iDayofYear < 142 Then

ZodiacSign = “Taurus”

ElseIf iDayofYear < 173 Then

ZodiacSign = “Gemini”

ElseIf iDayofYear < 205 Then

ZodiacSign = “Cancer”

ElseIf iDayofYear < 236 Then

ZodiacSign = “Leo”

ElseIf iDayofYear < 267 Then

ZodiacSign = “Virgo”

ElseIf iDayofYear < 297 Then

ZodiacSign = “Libra”

ElseIf iDayofYear < 327 Then

ZodiacSign = “Scorpio”

ElseIf iDayofYear < 357 Then

ZodiacSign = “Sagittarius”

Else

ZodiacSign = “Capricorn”

End If

End Function

When entered in a worksheet cell (=ZodiacSign(A1)), where A1 contains the birthdate in question, the correct sign of the zodiac will be returned. Note that this part of the function code

If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then

iDayofYear = iDayofYear – 1

End If

adjusts the day of year if the year of birth is a leap year.