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?
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
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
D
Diana
Well actaully i was thinking about all 12 of them...
is that possible?
is that possible?
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
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
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)
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).
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)
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)
=VLOOKUP(MONTH(A1)*100+DAY(A1),H1:I13,2)