IF formula for Zodiac sign and Chinese Zodiac

[momentsgoneby80]

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.

[kuovonne]

momentsgoneby80:

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

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.

[kuovonne]

momentsgoneby80:

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?

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

[momentsgoneby80]

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

[momentsgoneby80]

kuovonne:

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.

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.

[momentsgoneby80 · Accepted solution]

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: