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 {: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.

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

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