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		-

 

[Dave Patton]

The dates that you show in Column D look a lot like International Format dates. This column must have real dates.
You can custom format the dates. A format like yyyy-mmm-dd is not ambiguous.

The post below is based on your list of dates. Ensure that the dates that I show are the dates that you intended. These must be real dates and not text.

T202211x.xlsm
	E	F
1	dates	sign
2	1961-Jun-06	Taurus
3	1984-Apr-23	Aries
4	1956-Mar-15	Taurus
5	1981-Jul-01	Leo
6	1968-Feb-12	Capricorn
7	1960-Feb-13	Capricorn
8	1968-Apr-01	Pisces
9	1968-Dec-07	Capricorn
10	1978-Aug-21	Libra
11	1978-Apr-21	Taurus
12	1957-Jul-28	Virgo
1d_

Cell Formulas
Range	Formula
F2:F12	F2	=VLOOKUP(E2,B11:C1158,2,1)

 

[Dave Patton]

Dates/Times with General format

T202211x.xlsm
	A	B	C
1	Dates and times	Sign
2	10982.80694	Aquarius
3	10982	Aquarius
4	10981	#N/A	Not in lookup info
5	10981.9	Aquarius
6	11237	Scorpio
7	11005.54167	Aquarius	example with the time
8	11005.80764	Pisces	example with the time
1d_

Cell Formulas
Range	Formula
B2:B8	B2	=VLOOKUP(A2,$B$11:$C$1158,2,1)

Dates/Times with custom format yyyymmmdd hh:mm

T202211x.xlsm
	A	B	C
1	Dates and times	Sign
2	1930Jan24 19:22	Aquarius
3	1930Jan24 00:00	Aquarius
4	1930Jan23 00:00	#N/A	Not in lookup info
5	1930Jan23 21:36	Aquarius
6	1930Oct06 00:00	Scorpio
7	1930Feb16 13:00	Aquarius	example with the time
8	1930Feb16 19:23	Pisces	example with the time
9
1d_

Cell Formulas
Range	Formula
B2:B8	B2	=VLOOKUP(A2,$B$11:$C$1158,2,1)

 

[Dave Patton]

I edited the formula; this version may be more robust.

T202211x.xlsm
	A	B
11
12	Raw table dates	Date and Time
13	Jan 23, 1930 7:22 PM Venus enters Aquarius	1930-Jan-23 19:22
14	Feb 16, 1930 5:11 PM Venus enters Pisces	1930-Feb-16 17:11
15	Mar 12, 1930 5:34 PM Venus enters Aries	1930-Mar-12 17:34
16	Apr 5, 1930 9:57 PM Venus enters Taurus	1930-Apr-05 09:57
17	Apr 30, 1930 8:37 AM Venus enters Gemini	1930-Apr-30 08:37
18	May 25, 1930 12:36 AM Venus enters Cancer	1930-May-25 00:36
19	June 19, 1930 12:39 AM Venus enters Leo	1930-Jun-19 00:39
20	July 14, 1930 12:34 PM Venus enters Virgo	1930-Jul-14 12:34
21	Aug 9, 1930 18:54 Venus enters Libra	1930-Aug-09 18:54
22	Sep 7, 1930 12:05 AM Venus enters Scorpio	1930-Sep-07 12:05
23	Oct 11, 1930 9:45 PM Venus enters Sagittarius	1930-Oct-11 21:45
24
2a

Cell Formulas
Range	Formula
B13:B23	B13	=ConvertToDate(LEFT(A13,FIND("~",SUBSTITUTE(A13," ","~",5))))

 

[Dave Patton]

With some of the source data that you provided, there was a variety of extra spaces in the data.
The latest version of the formula uses the 5th space.
You can add a column for just the Date/Time portion and ensure that it is correct.
If you manually edit column A say to remove extra space, the results will show in the other columns.

T202211x.xlsm
	A	B	C	D
1		With 365	With earlier versions of Excel	Helper Column
2	Apr 2, 2011 12:51 AM Mars enters Aries	2011-Apr-02 12:51 AM	2011-Apr-02 12:51 AM	Apr 2, 2011 12:51 AM
3	Aug 3, 2011 5:22 AM Mars enters Cancer	2011-Aug-03 5:22 AM	2011-Aug-03 5:22 AM	Aug 3, 2011 5:22 AM
4	July 3, 2012 8:32 AM Mars enters Libra	2012-Jul-03 8:32 AM	2012-Jul-03 8:32 AM	July 3, 2012 8:32 AM
3dd

Cell Formulas
Range	Formula
B2:B4	B2	=ConvertToDate(TEXTBEFORE(A2," ",5))
C2:C4	C2	=ConvertToDate(LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",5))))
D2:D4	D2	=TEXTBEFORE(A2," ",5)

 

[doriannjeshi]

With some of the source data that you provided, there was a variety of extra spaces in the data.
The latest version of the formula uses the 5th space.
You can add a column for just the Date/Time portion and ensure that it is correct.
If you manually edit column A say to remove extra space, the results will show in the other columns.

T202211x.xlsm
	A	B	C	D
1		With 365	With earlier versions of Excel	Helper Column
2	Apr 2, 2011 12:51 AM Mars enters Aries	2011-Apr-02 12:51 AM	2011-Apr-02 12:51 AM	Apr 2, 2011 12:51 AM
3	Aug 3, 2011 5:22 AM Mars enters Cancer	2011-Aug-03 5:22 AM	2011-Aug-03 5:22 AM	Aug 3, 2011 5:22 AM
4	July 3, 2012 8:32 AM Mars enters Libra	2012-Jul-03 8:32 AM	2012-Jul-03 8:32 AM	July 3, 2012 8:32 AM
3dd

Cell Formulas
Range	Formula
B2:B4	B2	=ConvertToDate(TEXTBEFORE(A2," ",5))
C2:C4	C2	=ConvertToDate(LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",5))))
D2:D4	D2	=TEXTBEFORE(A2," ",5)

This is great
Thank you mr Patton!