# IF formula for Zodiac sign and Chinese Zodiac

Hi!
Iโm one of those that like organizing and solving things, but is horrid at learning things strictly through textbooks and other textbased material. I learn best through application and therfore have a base where I try all kinds of things for the purpose of learning.

I needed a new challenge and I usually try ideas that can be translated for other uses down the line. As I already have a column for { Birthday} in my base I thought I could have it calculate Zodiac sign and even Chinese Zodiac based on the date entered there. Now to the questions. As I have more than one question Iโve marked them as A1, A2, A3โฆ; B1, B2, B3โฆ for ease.

A - Zodiac sign
Iโm aweful at nesting formulas, so I came up with this that serves the purpose of finding the zodiac sign.

``````IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "01-01", DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "01-19"),
"ยซ โ Capricorn ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "01-20", DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "02-18"),
"ยซ โ Aquarius ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "02-19",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "03-20"),
"ยซ โ Pisces ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "03-21",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "04-19"),
"ยซ โ Aries ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "04-20",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "05-20"),
"ยซ โ Taurus ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "05-21",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "06-20"),
"ยซ โ Gemini ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "06-21",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "07-21"),
"ยซ โ Cancer ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "07-22",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "08-22"),
"ยซ โ Leo ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "08-23",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "09-22"),
"ยซ โ Virgo ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "09-23",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "10-22"),
"ยซ โ Libra ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "10-23",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "11-21"),
"ยซ โ Scorpio ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "11-22",DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "12-21"),
"ยซ โ Sagittarius ยป\n",'')
&''&IF(
AND(DATETIME_FORMAT({๐ Birthday}, 'MM/DD') >= "12-22", DATETIME_FORMAT({๐ Birthday}, 'MM/DD') <= "12-31"),
"ยซ โ Capricorn ยป\n",'')
``````

Itโs not elegant and at very first glance it looked like itโs working. However, it marks some of the dates correctly and some incorrectly.

EXAMPEL INCORRECT DATES

Date: 1995-07-05
Marks as: ยซ Leo ยป
Should be: ยซ Cancer ยป

Date: 1992-04-06
Marks as: ยซ Taurus ยป
Should be: ยซ Aries ยป

Date: 1990-11-10
Marks as: ยซ Sagittarius ยป
Should be: ยซ Scorpio ยป

Date: 1993-02-15
Marks as: ยซ Pisces ยป
Should be: ยซ Aquarius ยป

What I would like to know is

A1 - Is my approach to this flawed from the get go?
A2 - What causes the error?
A3 - How do I fix it?

I offer standing ovations for anyone who can show me if possible, and how so, to nest the formula.

B - Chinese Zodiac
Iโve tentatively atempted the formula for the Chinese Zodiac, but my tries either come up as not accepting the code or marking every year to be โ Horseโ. My tries have been variations of below code.

``````IF(
FIND(OR(DATETIME_FORMAT({๐ Birthday}, 'YYYY'), '1930', '1942', '1954', '1966', '1978', '1990', '2002', '2014', '2026')),
"๐ด Horse",'')
``````

What I would like to know is

B1 - Is my approach to this flawed from the get go?
B2 - As there are 12 Chinese Zodiac signs, do I best use an IF formula or would a SWITCH function be better?

Any and all help would be greatly appreciated.

Your approach makes sense, but it is more complicated than it needs to be. Notice that you arenโt actually nesting any of your `IF` statements. Rather, you are concatenating.

I suggest that you turn the birth month and day into a number, run comparisons on the numbers, and nest the `IF` statements.

``````IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 119,
"ยซ โ Capricorn ยป\n",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 218,
"ยซ โ Aquarius ยป\n",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 320,
"ยซ โ Pisces ยป\n",
...
)
)
)
``````

I donโt know why you are getting the incorrect sign for some values. It could be because Airtable has a very unique method for comparing strings with numbers.

1 Like

For the Chinese Zodiac, I think you are better off using a SWITCH function and a little math.

This formula should get you started. It takes the year and divides it by 12 and sees what the remainder is. Iโll let you figure out which animal goes with which remainder. If you use this, please reply with the final formula.

``````SWITCH(MOD(YEAR({๐ Birthday}), 12),
0, "animal0",
1, "animal1",
2, "animal2",
3, "animal3",
4, "animal4",
5, "animal5",
6, "animal6",
7, "animal7",
8, "animal8",
9, "animal9",
10, "animal10",
11, "animal11"
)

``````
2 Likes

@kuovonne For the Chinese zodiacs this is the final code. Itโs not an exact as Chinese zodiacs follow the Lunar Calendar and not the Gregorian Calendar that this formula calculates, but itโs close enough for my purposes.

``````SWITCH(MOD(YEAR({๐ Birthday}), 12),
0, "๐ต Monkey",
1, "๐ Rooster",
2, "๐ถ Dog",
3, "๐ท Pig",
4, "๐ญ Rat",
5, "๐ฎ Ox",
6, "๐ฏ Tiger",
7, "๐ฐ Rabbit",
8, "๐ฒ Dragon",
9, "๐ Snake",
10, "๐ด Horse",
11, "๐ Goat"
)
``````

Thank you for your help.

1 Like

The concaternating rather than nesting was simply because Iโm horrible at nesting. Iโm getting better though. Oddly enough it doesnโt throw erroneous outputs when nested.

Final code is:

``````IF({๐ Birthday},
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0119,
"โ Capricorn",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0218,
"โ Aquarius",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0320,
"โ Pisces",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0419,
"โ Aries",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0520,
"โ Taurus",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0620,
"โ Gemini",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0721,
"โ Cancer",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0822,
"โ Leo",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0922,
"โ Virgo",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1022,
"โ Libra",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1121,
"โ Scorpio",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1221,
"โ Sagittarius",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1231,
"โ Capricorn"
)
)
)
)
)
)
)
)
)
)
)
)
)
)
``````

It can most likely be tweaked for elegance, but it works for my purposes.

1 Like

Canโt mark two replies as โSolutionโ so Iโll combine them here.

Zodiac sign

``````IF({๐ Birthday},
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0119,
"โ Capricorn",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0218,
"โ Aquarius",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0320,
"โ Pisces",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0419,
"โ Aries",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0520,
"โ Taurus",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0620,
"โ Gemini",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0721,
"โ Cancer",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0822,
"โ Leo",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 0922,
"โ Virgo",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1022,
"โ Libra",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1121,
"โ Scorpio",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1221,
"โ Sagittarius",
IF(
VALUE(DATETIME_FORMAT({๐ Birthday}, 'MMDD')) <= 1231,
"โ Capricorn"
)
)
)
)
)
)
)
)
)
)
)
)
)
)
``````

Chinese Zodiac

``````SWITCH(	MOD(YEAR({๐ Birthday}),12),
0, "๐ต Monkey",
1, "๐ Rooster",
2, "๐ถ Dog",
3, "๐ท Pig",
4, "๐ญ Rat",
5, "๐ฎ Ox",
6, "๐ฏ Tiger",
7, "๐ฐ Rabbit",
8, "๐ฒ Dragon",
9, "๐ Snake",
10, "๐ด Horse",
11, "๐ Goat"
)
``````

Thank you so much @kuovonne for your help.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.