Skip to main content

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!

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.

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.


@Raminder_Singh


Raminder, I should have known I could count on you!!


Thank you so much. 🙂

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.




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')


)


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')


)



Thank you, @kuovonne !

Reply


Terms & Conditions