ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Excel formula (https://www.excelbanter.com/excel-discussion-misc-queries/9943-excel-formula.html)

Diana
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?

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

-----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?
.

Diana
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?
.

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

-----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?
.


.

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 :)

"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?
.


.

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)


"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?
.


.

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).

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?
.


.

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)


"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?
.


.

Myrna Larson
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?
.


.


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com