Home » Forums » Open Source Learning Center » Question and Answer

How to automatically get zodiac signs (based on date ranges)

Submitted by thorik on 18 September, 2012 - 09:13

Hello friends!!

I have a list of entries of people and their personal data which also include their birthdates. I also have an empty cell in each of their lines which i would like to have their zodiac sign automatically filled (through a formula)

I would like to have a seperate sheet with date ranges for the zodiac signs

THNAK YOU!!

‹ colour the row containing text "POSTED TO G/L" and above 3 rows with conditional formating Get Data using VBA ›
»

Get Your Zodiac Signs based on Date of Birth in Microsoft Excel

Submitted by Santosh Dubey on 2 March, 2013 - 11:00.

Hi Everyone,

I have completed and tested the following formula to find out Zodiac Signs based on Date of Birth or Date Ranges:

Here's the formula:
=CHOOSE(MATCH(1,(DATEVALUE({"12/22/2011","01/21/2012","02/20/2012","03/21/2012","04/21/2012","05/22/2012","06/22/2012","07/23/2012","08/23/2012","09/24/2012","10/24/2012","11/23/2012","12/22/2012"})<=DATEVALUE(TEXT(A1,"mm/dd/")&"2012"))*(DATEVALUE({"01/20/2012","02/19/2012","03/20/2012","04/20/2012","05/21/2012","06/21/2012","07/22/2012","08/22/2012","09/23/2012","10/23/2012","11/22/2012","12/21/2012","01/20/2013"})>=DATEVALUE(TEXT(A1,"mm/dd/")&"2012")),0),"Capricorn","Aquarius","Pisces","Aries","Taurus","Gemini","Cancer","Leo","Virgo","Libra","Scorpio","Sagittarius","Capricorn"))

I have a worksheet to for this calculations.

I program and work on Astrology Software based on Microsoft Excel and Visual basic

Contact on Indian day Time.

The Above Formula is OK and Tested By Mr. S.K. Dubey (Advanced Excel Expert)
Mobile: +91 - 9213032623
Email: 2sys@in.com]

»

RE: Get zodiac sign based on date

Submitted by manny on 19 September, 2012 - 12:33.

Hi,

Here's some solution. It is a bit [rather a quite :)] long formula but like that all is on one place and no need to have separate sheet with date ranges for the zodiac signs.

= INDEX(
 {"Capricorn", "Aquarius", "Pisces", "Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", "Scorpio", "Sagittarius", "Capricorn"},
 MATCH(1, (DATEVALUE(
 {"22.12.2011", "21.01.2012", "20.02.2012", "21.03.2012", "21.04.2012", "22.05.2012", "22.06.2012",
  "23.07.2012", "23.08.2012", "24.09.2012", "24.10.2012", "23.11.2012", "22.12.2012"})
 <= DATEVALUE(TEXT(A1, "dd.mm.") & "2012")) * (DATEVALUE(
 {"20.01.2012", "19.02.2012", "20.03.2012", "20.04.2012", "21.05.2012", "21.06.2012", "22.07.2012",
  "22.08.2012", "23.09.2012", "23.10.2012", "22.11.2012", "21.12.2012", "20.01.2013"})
 >= DATEVALUE(TEXT(A1, "dd.mm.") & "2012")), 0))

The years 2011, 2012, 2013 are not important. They may be, for example 1999, 2000, 2001 but must be used in the order in which I used them.

 

Best regards.

»

Thnx for the reply!! i get an

Submitted by thorik on 19 September, 2012 - 12:57.

Thnx for the reply!! i get an error in the formula
could you upload a working excel file maybe?!

»

RE: Get error in the formula

Submitted by manny on 19 September, 2012 - 13:27.

Hi,

What type of error you get?

Something like #N/A or #VALUE!?

Or you get error when you try to enter the formula?

Some guess:

 - Check your list separator. For example, mine is ";" but here I changed to "," because is most used in the other countries. Maybe yours is also ";" or another one and must replace all "," in the formula with it?

 - Check your date format. In some countries first is the Month then the Day and last the Year. Somewhere the Year is placed at front.

...

»

I am in Google Docs and i get

Submitted by thorik on 19 September, 2012 - 15:02.

I am in Google Docs and i get a parse error.

My date is on this format 22.12.82 . DD,MM,YY

Thanks again!

»

RE: Get zodiac signs in Google Docs

Submitted by manny on 19 September, 2012 - 20:53.

Hi,

I'm very sorry but I have no any experience with Google Docs and don't know how to get any other date format except MM/DD/YYYY. In fact, the most formulas are not working as expected. However, I modified the formula and I tested it on Google Docs. It works, but only with MM/DD/YYYY date format. If you know some way to change it to your used date format just make necessary modifications into the formula or you need to change the date format of your data.

Here's the formula:

= ARRAYFORMULA(CHOOSE(MATCH(1, (DATEVALUE({"12/22/2011", "01/21/2012", "02/20/2012", "03/21/2012", "04/21/2012", "05/22/2012", "06/22/2012", "07/23/2012", "08/23/2012", "09/24/2012", "10/24/2012", "11/23/2012", "12/22/2012"}) <= DATEVALUE(TEXT(A1, "mm/dd/") & "2012")) * (DATEVALUE({"01/20/2012", "02/19/2012", "03/20/2012", "04/20/2012", "05/21/2012", "06/21/2012", "07/22/2012", "08/22/2012", "09/23/2012", "10/23/2012", "11/22/2012", "12/21/2012", "01/20/2013"}) >= DATEVALUE(TEXT(A1, "mm/dd/") & "2012")), 0), "Capricorn", "Aquarius", "Pisces", "Aries", "Taurus", "Gemini", "Cancer", "Leo", "Virgo", "Libra", "Scorpio", "Sagittarius", "Capricorn"))

I'm sorry that I cannot help you more with this issue.

 

Best regards.

»

Thank you so much my friend!!

Submitted by thorik on 19 September, 2012 - 23:51.

Thank you so much my friend!!

»