Formula to determine the Zodiac sign

mgana

Board Regular
Joined
Jul 18, 2003
Messages
61
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?
 
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.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
mgana said:
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
 
Upvote 0
I'm using Aladin's formula as he has given. Could you please explain the --part in the TEXT function?
 
Upvote 0
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.
 
Upvote 0
No it does not. But its a start. This is a simple task with an apparently unique solution....when it comes!
 
Upvote 0
sdible said:
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.
 
Upvote 0
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)
 
Upvote 0
mgana said:
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.
 
Upvote 0
mgana said:
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top