Skip to main content

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.




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"

)






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.




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.




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: 👏 :woman_standing: 👏 :woman_standing: 👏 :woman_standing: 👏 :woman_standing: 👏 :woman_standing: 👏 :woman_standing: 👏


Reply