Extract vlook up dates

[doriannjeshi]

Hello,

I need to look up dates for a table in this format!

pyth new.xlsb
	I	J	K
1	1940	date	Sign
2	Jan 1 – Jan 31 Scorpio	1/15/1941	?
3	Feb 1 – Mar 25 Sagittarius
4	Mar 26 – Jun 5 Capricorn
5	June 6 – Sep 28 Sagittarius
6	Sep 29 – Nov 29 Capricorn
7	Nov 30 – Dec 31 Aquarius
8	1941
9	Jan 1 – Jan 20 Aquarius
10	Jan 21 – Mar 7 Pisces
11	Mar 8 – Apr 17 Aries
12	Apr 18 – May 24 Taurus
13	May 25 – Jun 28 Gemini
14	Jun 29 – Jul 31 Cancer
15	Aug 1 – Sep 1 Leo
16	Sep 2 – Oct 5 Virgo
17	Oct 6 – Nov 10 Libra
18	Nov 11 Scorpio
19	Dec 20 Sagittarius
20	1942
21	Jan 1 – Feb 2 Sagittarius
22	Feb 3 – Mar 26 Capricorn
23	Mar 27 – Dec 10 Aquarius
24	Dec 11 – Dec 31 Pisces
25	1943
26	Jan 1 – Jan 27 Pisces
27	Jan 28 – Mar 7 Aries
28	Mar 8 – Apr 11 Taurus
29	Apr 12 – May 14 Gemini
30	May 15 – Jun 16Cancer
31	Jun 17 – Jul 19 Leo
32	Jul 20 – Aug 23Virgo
33	Aug 24 – Sep 30 Libra
34	Oct 1 – Nov 11Scorpio
35	Nov 12 – Dec 27 Sagittarius
36	Dec 28 – Dec 31 Capricorn
37	1944
38	Jan 1 – Feb 15 Capricorn
39	Feb 16 – Apr 8 Aquarius
40	Apr 9 – Jun 4 Pisces
41	Jun 5 – Aug 8 Aries
42	Aug 9 – Nov 6 Taurus
43	Nov 7 – Dec 31 Aries
44	1945
eros&psyche

 

[Peter_SSs]

The 2 errors in the first table are because the months are not abbreviated to 3 letters.

Can you please post the other two again but use Mini Sheet so that I can see the rows/columns and the formulas?

 

[doriannjeshi]

Sorry for the not abbreviated months!

here are the only cases:

pyth new.xlsb
	G	H
5	#N/A	Jan 1 – Aries
6	#N/A	Jan 1 Capricorn
Sheet2

Cell Formulas
Range	Formula
G5:G6	G5	=DATEVALUE(MID(H5,5,2)&LEFT(H5,3)&LOOKUP(9999,H$1:H4))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G6	Other Type	Color scale		NO
G5	Other Type	Color scale		NO

 

[Peter_SSs]

Is there a year above that in column H? If so, there shouldn't be a problem.

22 12 03.xlsm
	G	H
4		1966
5	1/01/66	Jan 1 – Aries
6	1/01/66	Jan 1 Capricorn
Lookup 2

Cell Formulas
Range	Formula
G5:G6	G5	=DATEVALUE(MID(H5,5,2)&LEFT(H5,3)&LOOKUP(9999,H$1:H4))

However, if you were looking up Jan 1 for that year I'm not sure what logic you would use to decide which of those 2 signs to return since they both belong to Jan 1.

 

[doriannjeshi]

I cut pasted the dates that belong to diferent years , and yes both dates have a year , and then probably is my date system setting that causes this

 

[Peter_SSs]

I cut pasted the dates that belong to diferent years , and yes both dates have a year , and then probably is my date system setting that causes this

If you still need help with this then please post some small sample data from columns G:H, starting from row 1 & including the formula used in column G.