Skip to main content

Help with formula/script/etc for assigning Zodiac sign to birthdays

  • July 24, 2021
  • 4 replies
  • 30 views
Taylor_Johnston
Forum|alt.badge.img+11

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!

    4 replies

    R
    Forum|alt.badge.img+3
    • Raminder_Singh
    • Participating Frequently
    • July 25, 2021

    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
      Forum|alt.badge.img+11
      • Taylor_Johnston
      • Author
      • Known Participant
      • July 25, 2021

      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. :slightly_smiling_face:

        kuovonne
        Forum|alt.badge.img+29
        • kuovonne
        • Brainy
        • July 25, 2021

        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')
)
          Taylor_Johnston
          Forum|alt.badge.img+11
          • Taylor_Johnston
          • Author
          • Known Participant
          • July 25, 2021

          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 !

            Terms & ConditionsAccessibility statement