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		-

 

[doriannjeshi]

It works great mr Paton thank you

I am having a little trouble with dates it is not recognising the format, It works only if I manually type 23-jan-year. If anyone could help me do this automatically for a long list !

22/01/1963

10/09/1950

12/12/1987

20/09/1967

16/11/1985

19/08/1979

 

[Dave Patton]

Did you happen to read the questions in previous posts?
• 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.
• Please 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?

Does the suggestion work for you or not?
it works great mr Paton thank you
I am having a little trouble with dates it is not recognising the format, It works only if I manually type 23-jan-year. If anyone could help me do this automatically for a long list !

The Lookup Table currently just has useful data for 1930.
**** We need the list of venus information and we might have to convert the list to real dates and extract the sign information.

 

[jdellasala]

It works great mr Paton thank you

I am having a little trouble with dates it is not recognising the format, It works only if I manually type 23-jan-year. If anyone could help me do this automatically for a long list !

22/01/1963

10/09/1950

12/12/1987

20/09/1967

16/11/1985

19/08/1979

Not sure what you mean. If you use only 2 digits for the year, it will automatically change it to 19yy for 1930 or later, but change it to 20yy for values under 30. It's best to stick to 4 digit years.
Dates start from 1/1/1900 and cannot be used. Attempting to enter dates with text is unreliable. Use digits and either / or - to separate day/month/year, and only use 4 digit years.
Use Formatting to get text to display.

 

[Dave Patton]

A2 etc. must be Real Dates that Excel can use; the formatting does not change the values in the cell.

The Lookup table was edited to dates just for 1930; I did not see answers to our questions.
There may be a variety of inconsistencies in the source information and there is little value in continuing to guess!!

T202211x.xlsm
	A	B
1	Date	Sign
2	10988	Aquarius
3	04-May-1930	Gemini
4	1930-10-06	Scorpio
5
16ddd

Cell Formulas
Range	Formula
B2:B4	B2	=VLOOKUP(A2,B10:C20,2,1)

 

[doriannjeshi]

It works great mr Paton thank you

I am having a little trouble with dates it is not recognising the format, It works only if I manually type 23-jan-year. If anyone could help me do this automatically for a long list !

Did you happen to read the questions in previous posts?
• 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.
• Please 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?

Does the suggestion work for you or not?
it works great mr Paton thank you
I am having a little trouble with dates it is not recognising the format, It works only if I manually type 23-jan-year. If anyone could help me do this automatically for a long list !

The Lookup Table currently just has useful data for 1930.
**** We need the list of venus information and we might have to convert the list to real dates and extract the sign information.

Well, the formula that you provided me worked when I referenced instead of the 1930 the whole table of venus sign, 1,148 rows , because I tested some examples.
The planets have a table of dates when they enter in the signs so I am trying to find the sign that this planet falls in a given date, also the dates I have are in text I suspect and or in dd,mm,yyyy format and I am having trouble using it automatically.
this is the file I am working

CONVERSION.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[Dave Patton]

The VLookup works fine in my sheet and the conversions worked fine after minimal cleanup of the data.

1. we do not have adequate information on the source data
2. we have not received answers to our questions
3. we do not know the Regional Settings on your computer for dates

We cannot open files sent. The forum provides a tool named XL2BB that helps us to post extracts of our sheets to the forum.

 

[doriannjeshi]

Thank you for you patience!
1. I am posting the information that I am working on , the file I am working on and the whole source data.
3.Regional settings are English US obligatory for some software to work properly, though I try to use dates on my excel dd/mm/yyyy out of habit
As jdellasala instructions are to use only dates not text I need a little help with these dates

pyth new.xlsb
	A	B	C	D	E
2	Raw table dates	conversion	sign	dates	sign
3	Jan 23, 1930 7:22 PM Venus enters Aquarius	21/01/1930	Aquarius	06/06/1961	?
4	Feb 16, 1930 5:11 PM Venus enters Pisces	16/02/1930	Pisces	23/04/1984	?
5	Mar 12, 1930 5:34 PM Venus enters Aries	12/03/1930	Aries	15/03/1956	?
6	Apr 5, 1930 9:57 PM Venus enters Taurus	05/04/1930	Taurus	01/07/1981
7	Apr 30, 1930 8:37 AM Venus enters Gemini	30/04/1930	Gemini	12/02/1968
8	May 25, 1930 12:36 AM Venus enters Cancer	25/05/1930	Cancer	13/02/1960
9	June 19, 1930 12:39 AM Venus enters Leo	19/06/1930	Leo	01/04/1968
10	July 14, 1930 12:34 PM Venus enters Virgo	14/07/1930	Virgo	07/12/1968
11	Aug 9, 1930 8:54 PM Venus enters Libra	09/08/1930	Libra	21/08/1978
12	Sep 7, 1930 12:05 AM Venus enters Scorpio	07/09/1930	Scorpio	21/04/1978
13	Oct 11, 1930 9:45 PM Venus enters Sagittarius	11/10/1930	Sagittarius	28/07/1957
Sheet2

 

[doriannjeshi]

Xl2bb wont let me post the whole table of data, as requested

 

[jdellasala]

Xl2bb wont let me post the whole table of data, as requested

Is there a source for the data or maybe a web site with the table? I don't know what limits there are to Xl2bb, but over 1,000 rows probably goes over it!

 

[Dave Patton]

1. Your information previously indicated or implied International Format dd-mm-yy. That is the format that I use.
Since your system uses USA settings for date formats, I will move away from a formula solution.
2. Raw table includes times. I will include these in the solution as I did previously.
3. First step, Clean up the source information.
Change all June and July to Jun and Jul respectively. You can do this with Find and Replace.
4. Copy the UDF and put it in a regular module.
5. Try the formula in B. You can edit the UDF to capture the correct range of data but I left it alone.
6. Format the Date and time to your preference
7. Fill Down
8. If any rows did not convert, review the source information and edit as required.
9. The sign is shown in Columns C and D. Column C works with older versions of Excel.

Function ConvertToDate(S As String)
If Not IsDate(S) Then
    ConvertToDate = CVErr(xlErrNA)
    Exit Function
End If
ConvertToDate = DateValue(S) + TimeValue(S)
End Function

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

Cell Formulas
Range	Formula
B2:B6	B2	=VLOOKUP(A2,$B$13:$C$2000,2,1)
B13:B23	B13	=ConvertToDate(LEFT(A13,IFERROR(FIND("PM",A13)+1,FIND("AM",A13)+1)))
C13:C23	C13	=MID(A13,FIND("enters",A13)+7,20)
D13:D23	D13	=TEXTAFTER(A13,"enters ")