Formula to determine the Zodiac sign

[mgana]

I need a formula (no VBA) that will display the name of the zodiac sign in column B based on the date of birth (dd/mm/yyyy) entered in column A according to the following criteria:

Dec. 22 - Jan. 19 - Capricorn
Jan. 20 - Feb. 17 - Aquarius
Feb. 18 - Mar. 19 - Pisces
March 20 - April 19 - Aries
April 20 - May 19 - Taurus
May 20 - June 20 - Gemini
June 21 - July 21 - Cancer
July 22 - Aug. 22 - Leo
Aug 23 - Sept. 21 - Virgo
Sept. 22 - Oct. 22 - Libran
Oct. 23 - Nov. 21 - Scorpio
Nov. 22 - Dec. 21 - Sagittarius

Can this be done?

 

[mgana]

The vlookup formula works. There's just one thing - if the date format is changed it gives the wrong output - which is expected. Could you tell me how to edit the formula so it takes the format dd/mm/yy?

Thanks.

 

[eliW]

The vlookup formula works. There's just one thing - if the date format is changed it gives the wrong output - which is expected. Could you tell me how to edit the formula so it takes the format dd/mm/yy?

Thanks.

Hi Mgana,

Do you address your question to my solution (using a table) Or Aladin's improved formula?

Note that Aladin did not included the Value part in the formula and he put -- instead. I think that this is important.

Also I dont see the problem as my formula converts dates (not matter how formated) to TEXT "m.d".

Pls send the exact formula u are using.


Regards,

Eli

 

[mgana]

I'm using Aladin's formula as he has given. Could you please explain the --part in the TEXT function?

 

[BAlGaInTl]

This one interestests me now even thougth I don't really need it.

I noticed that this doesn't work as needed

=VLOOKUP(--TEXT(A1,"m.d"),{1.1,"Capricorn";1.2,"Aquarius";2.18,"Pisces";3.2,"Aries";
4.2,"Taurus";5.2,"Gemini";6.21,"Cancer";7.22,"Leo";8.23,"Virgo";
9.22,"Libran";10.23,"Scorpio";11.22,"Sagittarius";12.22,"Capricorn"},2,1)

Sometimes the closest match is not your actual zodiac sign. My birthdate is 11/06. It thinks I'm a Sagittarius when in fact I'm a Scorpio.

 

[mgana]

No it does not. But its a start. This is a simple task with an apparently unique solution....when it comes!

 

[Aladin Akyurek]

This one interestests me now even thougth I don't really need it.

I noticed that this doesn't work as needed

=VLOOKUP(--TEXT(A1,"m.d"),{1.1,"Capricorn";1.2,"Aquarius";2.18,"Pisces";3.2,"Aries";
4.2,"Taurus";5.2,"Gemini";6.21,"Cancer";7.22,"Leo";8.23,"Virgo";
9.22,"Libran";10.23,"Scorpio";11.22,"Sagittarius";12.22,"Capricorn"},2,1)

Sometimes the closest match is not your actual zodiac sign. My birthdate is 11/06. It thinks I'm a Sagittarius when in fact I'm a Scorpio.

You're indeed Scorpio. Corrected...

=VLOOKUP(--TEXT(E3,"m.dd"),{1.01,"Capricorn";1.2,"Aquarius";2.18,"Pisces";3.2,"Aries";4.2,
"Taurus";5.2,"Gemini";6.21,"Cancer";7.22,"Leo";8.23,"Virgo";9.22,
"Libran";10.23,"Scorpio";11.22,"Sagittarius";12.22,"Capricorn"},2,1)

Thanks for testing the formula.

 

[BAlGaInTl]

Ahh... I figured out another solution, but it isn't nearly as easy.

=VLOOKUP(--TEXT(A1,"m.d"),{12.22,"Capricorn";1.1,"Capricorn";1.19,"Capricorn";1.2,"Aquarius";2.17,"Aquarius";2.18,"Pisces";3.19,"Pisces";3.2,"Aries";4.19,"Aries";4.2,"Taurus";5.19,"Taurus";5.2,"Gemini";6.2,"Gemini";6.21,"Cancer";7.21,"Cancer";7.22,"Leo";8.22,"Leo";8.23,"Virgo";9.21,"Virgo";9.22,"Libran";10.22,"Libran";10.23,"Scorpio";11.21,"Scorpio";11.22,"Sagittarius";12.21,"Sagittarius"},2,1)

 

[Aladin Akyurek]

Ahh... I figured out another solution, but it isn't nearly as easy. ...

The clue is "m.dd", obviously. Your birth date should be represented as 11.06, not as 11.6.

 

[Aladin Akyurek]

I'm using Aladin's formula as he has given. Could you please explain the --part in the TEXT function?

-- coerces a text formatted number into a true number.

The TEXT(date,"m.dd") returns a text-formatted number of which ISNUMBER would be FALSE.
The VALUE function or -- turns into a number. The latter operates faster.

 

[Aladin Akyurek]

The vlookup formula works. There's just one thing - if the date format is changed it gives the wrong output - which is expected. Could you tell me how to edit the formula so it takes the format dd/mm/yy?

Thanks.

Are you sure? If the birth date is a true date, TEXT(birth-date,"m.dd")
should manage to get the right number. Check whehter ISNUMBER
is true of the birth date you enter.