Dates Within A Range

[Kip]

I have birthdates in a spreadsheet two ways.

A1=Birth Mont
A2=Birth Day

or

A3=mm/dd/yyyy

I am trying to calculate zodiac sign from this, so I want to enter a
formula for each sign to see if the date falls within a specific date
range. For example, any birthdates from 03/21-04/20 would be Aires. I
tried entering formula as follows:

IF(A1=3)AND(A2>=21)OR(A1=4)AND(A2<=20),1,0

This does not work. Any suggestions either using the two column method
of A1 & A2 cells or the combined bithdate in cell A3?

Thanks.

 

[Don Guillett]

where your daterange is a proper date and your start date in b1, end date in
b2
if(and(a3>=b1,a3<b2),1,"")

to count all
=sumproduct(a2:a22>=b1)*(a2:a22<b2))

 

[Guest]

You could always reverse this and use this formula to give the Zodiac sign
for each birthdate

=LOOKUP(A1+A2/100,{1.01,"Capricorn";1.2,"Aquarius";2.18,"Pisces";3.2,"Aries";4.2,
"Taurus";5.2,"Gemini";6.21,"Cancer";7.22,"Leo";8.23,"Virgo";9.22,
"Libran";10.23,"Scorpio";11.22,"Sagittarius";12.22,"Capricorn"})