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.
