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.