IF formula for Zodiac sign and Chinese Zodiac

[Taylor_Johnston]

I’m not very tech-friendly, so I’m not sure what the best way to go about this request would be - any advice is very much appreciated!

I keep all of my orders in a table - each individual birthdate is imported with each order in its own column (DOB). I would like to find a solution to automatically update a separate column (ZODIAC) with the corresponding Zodiac sign depending on the day/month of birth.
I can’t seem to make it an automation in Airtable with if/then parameters because I can’t select a date range without the Year included.

Does this make sense? Any tips?
Thanks in advance!

[Raminder_Singh]

Hey @Taylor_Johnston,

You can change the ZODIAC field to a formula field and use the following formula in it:

SWITCH(MONTH({DOB}),
  1, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 19), 'Capricorn', 'Aquarius'),
  2, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 18), 'Aquarius', 'Pisces'),
  3, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 20), 'Pisces', 'Aries'),
  4, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 19), 'Aries', 'Taurus'),
  5, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 20), 'Taurus', 'Gemini'),
  6, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 21), 'Gemini', 'Cancer'),
  7, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Cancer', 'Leo'),
  8, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Leo', 'Virgo'),
  9, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Virgo', 'Libra'),
  10, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Libra', 'Scorpio'),
  11, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Scorpio', 'Sagittarius'),
  12, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 21), 'Sagittarius', 'Capricorn')
)

To elaborate a bit on how the formula works – the idea is to split each month in two and assign a sign to each part. For example, January 1 to 19 is Capricorn and the rest of January is Aquarius.

[Taylor_Johnston]

@Raminder_Singh
Raminder, I should have known I could count on you!!
Thank you so much. :slightly_smiling_face:

[kuovonne]

Raminder_Singh:

You can change the ZODIAC field to a formula field and use the following formula in it:

Since the day will always be at least one, the formula can be tightened up a bit:

SWITCH(MONTH({DOB}),
  1, IF(DAY({DOB}) <= 19, 'Capricorn', 'Aquarius'),
  2, IF(DAY({DOB}) <= 18, 'Aquarius', 'Pisces'),
  3, IF(DAY({DOB}) <= 20, 'Pisces', 'Aries'),
  4, IF(DAY({DOB}) <= 19, 'Aries', 'Taurus'),
  5, IF(DAY({DOB}) <= 20, 'Taurus', 'Gemini'),
  6, IF(DAY({DOB}) <= 21, 'Gemini', 'Cancer'),
  7, IF(DAY({DOB}) <= 22, 'Cancer', 'Leo'),
  8, IF(DAY({DOB}) <= 22, 'Leo', 'Virgo'),
  9, IF(DAY({DOB}) <= 22, 'Virgo', 'Libra'),
  10, IF(DAY({DOB}) <= 22, 'Libra', 'Scorpio'),
  11, IF(DAY({DOB}) <= 22, 'Scorpio', 'Sagittarius'),
  12, IF(DAY({DOB}) <= 21, 'Sagittarius', 'Capricorn')
)

[Taylor_Johnston]

Raminder_Singh:

SWITCH(MONTH({DOB}),
  1, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 19), 'Capricorn', 'Aquarius'),
  2, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 18), 'Aquarius', 'Pisces'),
  3, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 20), 'Pisces', 'Aries'),
  4, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 19), 'Aries', 'Taurus'),
  5, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 20), 'Taurus', 'Gemini'),
  6, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 21), 'Gemini', 'Cancer'),
  7, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Cancer', 'Leo'),
  8, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Leo', 'Virgo'),
  9, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Virgo', 'Libra'),
  10, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Libra', 'Scorpio'),
  11, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 22), 'Scorpio', 'Sagittarius'),
  12, IF(AND(DAY({DOB}) >= 1, DAY({DOB}) <= 21), 'Sagittarius', 'Capricorn')
)

Thank you, @kuovonne !