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?
 

mgana

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
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
 

mgana

Board Regular
Joined
Jul 18, 2003
Messages
61
I'm using Aladin's formula as he has given. Could you please explain the --part in the TEXT function?
 

BAlGaInTl

Well-known Member
Joined
May 7, 2003
Messages
1,082
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

Board Regular
Joined
Jul 18, 2003
Messages
61

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

BAlGaInTl

Well-known Member
Joined
May 7, 2003
Messages
1,082

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
sdible said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,026
Messages
5,639,615
Members
417,101
Latest member
amoverton2

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
Top