Excel formula

D

Diana

If dates are 3/21 to 4/19 = Aries
and if dates are 7/23 to 8/22 = Leo

How do I make a valid excel formula?
 
B

Biff

Hi!

If all you want to check for is those two and the date is
entered in A1:

A1 = date

=IF(AND(A1>=DATE(YEAR(TODAY()),3,21),A1<=DATE(YEAR(TODAY
()),4,19)),"Aries",IF(AND(A1>=DATE(YEAR(TODAY
()),7,23),A1<=DATE(YEAR(TODAY()),8,22)),"Leo",""))

If you want all 12 then a different approach will be
needed.

Biff
 
B

Biff

Well actaully i was thinking about all 12 of them...

Yeah, I kind of figured that! <g>

Well, I don't know all the dates or the other signs.

Biff
 
D

Diana

Here are the dates:
Aries - 3/21 - 4/19
Pisces - 2/19 - 3/20
Taurus - 4/20 - 5/20
Gimini - 5/21 - 6/21
Cancer - 6/22 - 7/22
Leo - 7/23 - 8/22
Virgo - 8/23 - 9/22
Libra - 9/23 - 10/22
Scorpio - 10/23 - 11/21
Sagittarius - 11/22 - 12/21
Capricorn - 12/22 - 1/19
Aquarius - 1/20 - 2/18

Thank you so much for helping :)
 
B

Bob Phillips

Create this table somewhere on the spreadsheet, such as H1:I12

Aquarius 20
Pisces 19
Aries 21
Taurus 20
Gemini 21
Cancer 22
Leo 23
Virgo 22
Libra 23
Scorpio 23
Sagittarius 22
Capricorn 22


Then, assuming your test date is in A1, use this formula

=IF(DAY(A1)<INDEX($I$1:$I$12,MONTH(A1)),INDEX($H$1:$H$12,MONTH(DATE(YEAR(A1)
,MONTH(A1)-1,1))),INDEX($H$1:$H$12,MONTH(A1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Myrna Larson

I would create a table that looks like this:

101 Capricorn
120 Aquarius
219 Pisces
321 Aries
420 Taurus
521 Gemini
622 Cancer
723 Leo
823 Virgo
923 Libra
1023 Scorpio
1122 Sagittarius
1222 Capricorn

If that table is in H1:I13 and the date in A1, the formula is

=VLOOKUP(MONTH(A1)*100+DAY(A1,H1:I13,2)

Note that the signs must be in chronological order (you have Aries and Pisces
interchanged in your list).
 
B

Bob Phillips

Hi Myrna,

Think you missed a bracket

=VLOOKUP(MONTH(A1)*100+DAY(A1),H1:I13,2)


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Myrna Larson

Oops, I see a typo (missing paren). Formula should be

=VLOOKUP(MONTH(A1)*100+DAY(A1),H1:I13,2)
 
Top