Calculate date

[doriannjeshi]

Hello,

Is there a formula to calculate the specific sign knowing when a sign enters the date ?

period	sign		date	sign
23/01/1930	Aquarius		08/12/1930	?
16/02/1930	Pisces		04/05/1930	?
12/03/1930	Aries		06/01/1930	?
05/04/1930	Taurus		-
30/04/1930	Gemini		-
25/05/1930	Cancer		-
19/06/1930	Leo		-
14/07/1930	Virgo		-
09/08/1930	Libra		-
07/09/1930	Scorpio		-
11/10/1930	Sagittarius		-
22/11/1930	Scorpio		-
03/01/1931	Sagittarius		-

 

[rlv01]

This looks promising

Zodiac sign lookup

The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here.

exceljet.net

 

[Dave Patton]

Check the dates specified with the definitions that you use.

I provided a lookup with table or without the table.

T202211.xlsm
	A	B	C	D
1	Date	Sign	Sign
2	8-Dec-1930	Sagittarius	Sagittarius
3	4-May-1930	Taurus	Taurus
4	6-Jan-1930	Capricorn	Capricorn
5	20-Mar-1930	Pisces	Pisces
6
7			22-Dec-29	Capricorn
8			20-Jan-30	Aquarius
9			19-Feb-30	Pisces
10			21-Mar-30	Aries
11			20-Apr-30	Taurus
12			21-May-30	Gemini
13			21-Jun-30	Cancer
14			23-Jul-30	Leo
15			23-Aug-30	Virgo
16			23-Sep-30	Libra
17			23-Oct-30	Scorpio
18			22-Nov-30	Sagittarius
19
1d

Cell Formulas
Range	Formula
B2:B5	B2	=LOOKUP(A2,$C$7:$D$18)
C2:C5	C2	=LOOKUP(A2,{10949,"Capricorn";10978,"Aquarius";11008,"Pisces";11038,"Aries";11068,"Taurus";11099,"Gemini";11130,"Cancer";11162,"Leo";11193,"Virgo";11224,"Libra";11254,"Scorpio";11284,"Sagittarius"})

 

[Dave Patton]

With the approach mentioned in post #2, I named the lookup information "aL".

T202211.xlsm
	A	B
1	Date	Sign
2	8-Dec-1930	Sagittarius
3	4-May-1930	Taurus
4	6-Jan-1930	Capricorn
5	20-Mar-1930	Pisces
6	15-Jan-2022	Capricorn
7	20-Mar-2022	Pisces
8	25-Sep-2022	Libra
1d

Cell Formulas
Range	Formula
B2:B8	B2	=VLOOKUP(--(TEXT(A2,"m.dd")),aL,2,1)

To name the array of lookup information, access Formulas Name Manager
- new name aL
- value ={1.01,"Capricorn";1.2,"Aquarius";2.19,"Pisces";3.21,"Aries";4.2,"Taurus";5.21,"Gemini";6.21,"Cancer";7.23,"Leo";8.23,"Virgo";9.23,"Libra";10.23,"Scorpio";10.23,"Sagittarius";12.22,"Capricorn"}

 

[doriannjeshi]

I am sorry I wasn't clear, this is the table for the venus sign, is not the traditional sun sign

i posted the fist 20 records of the venus in the sign dates

 

[jdellasala]

I put together a 13 row table of the 12 signs plus an extra row for the the next year (what that means should become clear). I'm also using the year 1900 not only because it's easier to use.
The sample below uses SORT(RANDARRAY... to generate dates on every recalc. I included a VLOOKUP version in case you don't have Excel 2021 or MS 365. You can note what versions you're using in the About section of your profile.

It seems to be working. Let me know what you think.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Sign	Start Mnth	Start Month	Start Day	Start		Date	Sign		Start	Sign		Date	Sign
3	Aquarius	Jan	1	21	01/21/1900		02/15/2022	Pisces		01/01/1900	Aquarius		01/25/2022	Aquarius
4	Pisces	Feb	2	19	02/19/1900		02/22/2022	Pisces		01/21/1900	Aquarius		02/04/2022	Aquarius
5	Aries	Mar	3	21	03/21/1900		03/31/2022	Aries		02/19/1900	Pisces		02/04/2022	Aquarius
6	Taurus	Apr	4	20	04/20/1900		04/24/2022	Taurus		03/21/1900	Aries		02/06/2022	Aquarius
7	Gemini	May	5	21	05/21/1900		04/26/2022	Taurus		04/20/1900	Taurus		02/08/2022	Aquarius
8	Cancer	Jun	6	21	06/21/1900		05/14/2022	Gemini		05/21/1900	Gemini		03/19/2022	Pisces
9	Leo	Jul	7	23	07/23/1900		05/16/2022	Gemini		06/21/1900	Cancer		03/22/2022	Aries
10	Virgo	Aug	8	23	08/23/1900		05/29/2022	Gemini		07/23/1900	Leo		05/15/2022	Taurus
11	Libra	Sep	9	23	09/23/1900		07/13/2022	Leo		08/23/1900	Virgo		05/28/2022	Gemini
12	Scorpio	Oct	10	23	10/23/1900		07/26/2022	Leo		09/23/1900	Libra		06/08/2022	Gemini
13	Sagittarius	Nov	11	22	11/22/1900		08/15/2022	Virgo		10/23/1900	Scorpio		06/17/2022	Gemini
14	Capricorn	Dec	12	21	12/21/1900		08/15/2022	Virgo		11/22/1900	Sagittarius		06/29/2022	Cancer
15	Aquarius	Jan	1	21	01/21/1901		08/22/2022	Virgo		12/21/1900	Capricorn		07/16/2022	Cancer
16							08/31/2022	Virgo		01/21/1901	Aquarius		08/13/2022	Leo
17							09/09/2022	Libra					08/13/2022	Leo
18							09/26/2022	Libra					08/17/2022	Leo
19							11/19/2022	Sagittarius					08/18/2022	Leo
20							11/25/2022	Sagittarius					08/26/2022	Virgo
21							12/05/2022	Capricorn					10/19/2022	Libra
22							12/05/2022	Capricorn					10/26/2022	Scorpio
23
24					Formula in H3: =XLOOKUP(DATE(1900,MONTH(G3#),DAY(G3)),Zodiac[Start],Zodiac[Sign],,1)
25
26					Formula in N3: =VLOOKUP(DATE(1900,MONTH(M3),DAY(M3)),Zod2,2,TRUE)
27
Sheet3

Cell Formulas
Range	Formula
G3:G22,M3:M22	G3	=SORT(RANDARRAY(20,,DATEVALUE("1/1/2022"),DATEVALUE("12/31/2022"),TRUE))
H3:H22	H3	=XLOOKUP(DATE(1900,MONTH(G3#),DAY(G3)),Zodiac[Start],Zodiac[Sign],,1)
B3:B15	B3	=DATE(1900,[@[Start Month]],1)
N3:N22	N3	=VLOOKUP(DATE(1900,MONTH(M3),DAY(M3)),Zod2,2,TRUE)
Dynamic array formulas.

 

[doriannjeshi]

Thank you for the solutions , which seem to be for the traditional recurring sun sign ,

Instead the Venus sign has it's own table 1000 rows of dates when it entered a specific sign

This date table needs to be used to get the sign for a specific date

I posted the first records of this table in the first post

do I need to post the whole table of 1148 rows?

 

[jdellasala]

Thank you for the solutions , which seem to be for the traditional recurring sun sign ,

Instead the Venus sign has it's own table 1000 rows of dates when it entered a specific sign

This date table needs to be used to get the sign for a specific date

I posted the first records of this table in the first post

do I need to post the whole table of 1148 rows?

Not sure what those 1,000 rows are or how they relate to the date signs. At the least you'd need a table defining how Venus changes the values in the table above. I also don't understand how you went from 1,000 rows to 1,148 rows as my table has only 14 rows. Also, what about the other planets etc. Much more detail would be needed to proceed.

 

[Dave Patton]

to doriannjeshi​

Plese explain what you require.

How is the Venus sign calculated?
or

Do you want to use the 1000 +/- rows of data in Excel?
- where is this list of dates? Is there a download available?
- do you want to use a Lookup to secure the sign from a date?
- do you want help converting this list to real dates that you can use?
-

 

[Dave Patton]

T202211x.xlsm
	A	B	C
1	Date	Sign
2	30-Jan-1930	Aquarius
3	04-May-1930	Gemini
4	06-Oct-1930	Scorpio
5
6
7	Jan 23, 1930 7:22 PM Venus enters Aquarius	23-Jan-1930 19:22	Aquarius
8	Feb 16, 1930 5:11 PM Venus enters Pisces	16-Feb-1930 17:11	Pisces
9	Mar 12, 1930 5:34 PM Venus enters Aries	12-Mar-1930 17:34	Aries
10	Apr 5, 1930 9:57 PM Venus enters Taurus	5-Apr-1930 21:57	Taurus
11	Apr 30, 1930 8:37 AM Venus enters Gemini	30-Apr-1930 08:37	Gemini
12	May 25, 1930 12:36 AM Venus enters Cancer	25-May-1930 00:36	Cancer
13	June 19, 1930 12:39 AM Venus enters Leo	19-Jun-1930 00:39	Leo
14	July 14, 1930 12:34 PM Venus enters Virgo	14-Jul-1930 12:34	Virgo
15	Aug 9, 1930 8:54 PM Venus enters Libra	9-Aug-1930 20:54	Libra
16	Sep 7, 1930 12:05 AM Venus enters Scorpio	7-Sep-1930 00:05	Scorpio
17	Oct 11, 1930 9:45 PM Venus enters Sagittarius	11-Oct-1930 21:45	Sagittarius
18
Venus

Cell Formulas
Range	Formula
B2:B4	B2	=VLOOKUP(A2,B7:C1997,2,1)