| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Excel formula
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? |
|
#2
|
|||
|
|||
|
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 -----Original Message----- 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? . |
|
#3
|
|||
|
|||
|
Well actaully i was thinking about all 12 of them...
is that possible? "Biff" wrote: 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 -----Original Message----- 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? . |
|
#4
|
|||
|
|||
|
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 -----Original Message----- Well actaully i was thinking about all 12 of them... is that possible? "Biff" wrote: 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 -----Original Message----- 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? . . |
|
#5
|
|||
|
|||
|
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 :) "Biff" wrote: 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 -----Original Message----- Well actaully i was thinking about all 12 of them... is that possible? "Biff" wrote: 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 -----Original Message----- 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? . . |
|
#6
|
|||
|
|||
|
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) "Diana" wrote in message ... 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 :) "Biff" wrote: 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 -----Original Message----- Well actaully i was thinking about all 12 of them... is that possible? "Biff" wrote: 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 -----Original Message----- 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? . . |
|
#7
|
|||
|
|||
|
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). On Wed, 26 Jan 2005 00:53:02 -0800, "Diana" wrote: 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 :) "Biff" wrote: 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 -----Original Message----- Well actaully i was thinking about all 12 of them... is that possible? "Biff" wrote: 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 -----Original Message----- 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? . . |
|
#8
|
|||
|
|||
|
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) "Myrna Larson" wrote in message ... 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). On Wed, 26 Jan 2005 00:53:02 -0800, "Diana" wrote: 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 :) "Biff" wrote: 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 -----Original Message----- Well actaully i was thinking about all 12 of them... is that possible? "Biff" wrote: 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 -----Original Message----- 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? . . |
|
#9
|
|||
|
|||
|
Oops, I see a typo (missing paren). Formula should be
=VLOOKUP(MONTH(A1)*100+DAY(A1),H1:I13,2) On Wed, 26 Jan 2005 09:20:50 -0600, Myrna Larson wrote: 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). On Wed, 26 Jan 2005 00:53:02 -0800, "Diana" wrote: 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 :) "Biff" wrote: 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 -----Original Message----- Well actaully i was thinking about all 12 of them... is that possible? "Biff" wrote: 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 -----Original Message----- 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? . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
| How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
| Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
| Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
Linear Mode
