Star Sign Calculation

Hi All

I need to calculate what the star sign is for a list of individual references.

The first sheet has:

Column A - References
Column B - Date of Birth (not all references have an entry in column B)
Column C - What the star sign is (need formula for this in relation to column B)

the second sheet has:

A named table, 'Sign' in relation to star signs. First column is the sign, second is the start date of the sign (dd.mm). However cant stop year being added in actual entry.

All dates are in english format, i.e. dd/mm/yyyy

Can somebody please work out how to calculate the star sign. (on sheet please)

Have attached my workbook.

Thank you

The start date in your table may look like dd.mm (actually, "mmm-dd"), but it actually contains a date - these all start on 1st of the month, but are for different years. I thought star signs tend to start about the 19th/20th of a month, so can you supply me with the exact start dates (year is irrelevant) for each sign, and then I can set this up for you.

Hope this helps.

Pete

Originally Posted by Pete_UK

The start date in your table may look like dd.mm (actually, "mmm-dd"), but it actually contains a date - these all start on 1st of the month, but are for different years. I thought star signs tend to start about the 19th/20th of a month, so can you supply me with the exact start dates (year is irrelevant) for each sign, and then I can set this up for you.

Hope this helps.

Pete

Aries
March 21 -
April 19

Taurus
April 20 -
May 20

Gemini
May 21 -
June 20

Cancer
June 21 -
July 22

Leo
July 23 -
August 22

Virgo
August 23 -
September 22

Libra
September 23 -
October 22

Scorpio
October 23 -
November 21

Sagittarius
November 22 -
December 21

Capricorn
December 22 -
January 19

Aquarius
January 20 -
February 18

Pisces
February 19 -
March 20

Thanks

here's another option.

I too placed your second sheet in order and changed the years of the dates

I made all the dates relate to the year 2000, so the formula in C2 of the Data sheet is:

=IF(B2="","",INDEX('Star Sign'!$A$2:$A$14,MATCH(DATE(2000,MONTH(B2),DAY(B2)),'Star Sign'!$B$2:$B$14)))

Hope this helps.

Pete