Skip to main content
Solved

IF formula for Zodiac sign and Chinese Zodiac

M
Forum|alt.badge.img+12

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 { :id: 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: Leo »
Should be: « :cancer: Cancer »

Date: 1992-04-06
Marks as: « :taurus: Taurus »
Should be: « :aries: Aries »

Date: 1990-11-10
Marks as: « :sagittarius: Sagittarius »
Should be: « :scorpius: Scorpio »

Date: 1993-02-15
Marks as: « :pisces: Pisces »
Should be: « :aquarius: 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: 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.

Best answer by momentsgoneby80

momentsgoneby80 wrote:

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.


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. :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap:

View original
    Did this topic help you find an answer to your question?

    5 replies

    kuovonne
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6002 replies
    • March 31, 2020

    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.

    Justin_Barrett
    1 person likes this
    • Justin_Barrett
      Justin_Barrett
    kuovonne
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6002 replies
    • March 31, 2020

    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"
)
    S
    Justin_Barrett
    2 people like this
    • S
      Sam_Cederwall
    • Justin_Barrett
      Justin_Barrett
    M
    Forum|alt.badge.img+12
    kuovonne wrote:

    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"
)

    @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.

    kuovonne
    1 person likes this
    • kuovonne
      kuovonne
    M
    Forum|alt.badge.img+12
    kuovonne wrote:

    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.


    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.

    kuovonne
    1 person likes this
    • kuovonne
      kuovonne
    M
    Forum|alt.badge.img+12
    momentsgoneby80 wrote:

    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.


    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. :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap: :woman_standing: :clap:

    Reply


    Most helpful members this week

    Terms & Conditions