extract dates

D

doriannjeshi

Active Member
Joined
Apr 5, 2015
Messages
301
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have a very long list of dates, 3500 rows in these format:
Jan-00Feb-00Mar-00Apr-00May-00Jun-00
02 21:26 Aqu01 07:48 Pis02 18:03 Ari01 05:01 Tau02 22:24 Can01 15:44 Leo
04 22:09 Pis03 07:38 Ari04 18:25 Tau03 07:14 Gem05 07:00 Leo04 02:34 Vir
06 23:45 Ari05 09:42 Tau06 22:05 Gem05 13:16 Can07 18:35 Vir06 14:59 Lib
09 03:26 Tau07 15:07 Gem09 05:45 Can07 23:10 Leo10 07:09 Lib09 02:45 Sco
11 09:36 Gem09 23:50 Can11 16:38 Leo10 11:24 Vir12 18:41 Sco11 12:05 Sag
13 18:06 Can12 10:49 Leo14 05:04 Vir13 00:01 Lib15 04:07 Sag13 18:31 Cap
16 04:31 Leo14 23:00 Vir16 17:39 Lib15 11:38 Sco17 11:19 Cap15 22:38 Aqu
18 16:27 Vir17 11:36 Lib19 05:34 Sco17 21:38 Sag19 16:30 Aqu18 01:27 Pis
21 05:07 Lib19 23:44 Sco21 16:03 Sag20 05:36 Cap21 20:01 Pis20 03:57 Ari
23 16:54 Sco22 09:53 Sag23 23:57 Cap22 11:05 Aqu23 22:21 Ari22 06:53 Tau
26 01:50 Sag24 16:32 Cap26 04:25 Aqu24 13:59 Pis26 00:20 Tau24 10:52 Gem
28 06:47 Cap26 19:16 Aqu28 05:42 Pis26 15:00 Ari28 03:06 Gem26 16:28 Can
30 08:14 Aqu28 19:05 Pis30 05:13 Ari28 15:34 Tau30 07:55 Can29 00:18 Leo
30 17:30 Gem
Jul-00Aug-00Sep-00Oct-00Nov-00Dec-00
01 10:42 Vir02 19:08 Sco01 13:48 Sag01 05:56 Cap01 23:06 Pis01 08:22 Ari
03 22:59 Lib05 06:00 Sag03 22:27 Cap03 12:03 Aqu04 00:27 Ari03 10:01 Tau
06 11:11 Sco07 13:14 Cap06 02:53 Aqu05 14:22 Pis06 00:25 Tau05 11:27 Gem
08 21:04 Sag09 16:32 Aqu08 03:47 Pis07 14:07 Ari08 00:50 Gem07 14:04 Can
11 03:27 Cap11 17:11 Pis10 03:00 Ari09 13:17 Tau10 03:32 Can09 19:18 Leo
13 06:41 Aqu13 17:09 Ari12 02:45 Tau11 14:02 Gem12 09:48 Leo12 04:03 Vir
15 08:12 Pis15 18:24 Tau14 04:57 Gem13 18:01 Can14 19:47 Vir14 15:48 Lib
17 09:38 Ari17 22:14 Gem16 10:39 Can16 01:53 Leo17 08:08 Lib17 04:33 Sco
19 12:16 Tau20 04:56 Can18 19:38 Leo18 12:51 Vir19 20:48 Sco19 15:53 Sag
21 16:48 Gem22 14:02 Leo21 06:53 Vir21 01:24 Lib22 08:08 Sag22 00:32 Cap
23 23:20 Can25 00:57 Vir23 19:19 Lib23 14:04 Sco24 17:25 Cap24 06:34 Aqu
26 07:49 Leo27 13:12 Lib26 08:06 Sco26 01:50 Sag27 00:30 Aqu26 10:46 Pis
28 18:17 Vir30 02:03 Sco28 20:10 Sag28 11:47 Cap29 05:23 Pis28 14:02 Ari
31 06:29 Lib30 19:01 Aqu30 16:55 Tau


I need to fix the data in a single column

02 21:26 Aqu1/2/1900 21:26Aqu
04 22:09 Pis1/4/1900 3:07Pis
06 23:45 Ari1/6/1900 6:23Ari
09 03:26 Tau1/9/1900 6:23Tau


I am getting this data from a site:
witcheslore.com

Moon Sign Chart - Witches Lore

To find your Moon sign simply look up your year of birth and your month of birth, then you will know which sign of the Zodiac the Moon was in
witcheslore.com witcheslore.com
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Is this fine for you?

Book1
ABCDEFGHIJKLMN
11326192122153
202 21:26 Aqu01 07:48 Pis02 18:03 Ari01 05:01 Tau02 22:24 Can01 15:44 Leo02 21:2601 07:4802 18:0301 05:0102 22:2401 15:44
304 22:09 Pis03 07:38 Ari04 18:25 Tau03 07:14 Gem05 07:00 Leo04 02:34 Vir04 22:0903 07:3804 18:2503 07:1405 07:0004 02:34
406 23:45 Ari05 09:42 Tau06 22:05 Gem05 13:16 Can07 18:35 Vir06 14:59 Lib06 23:4505 09:4206 22:0505 13:1607 18:3506 14:59
509 03:26 Tau07 15:07 Gem09 05:45 Can07 23:10 Leo10 07:09 Lib09 02:45 Sco09 03:2607 15:0709 05:4507 23:1010 07:0909 02:45
611 09:36 Gem09 23:50 Can11 16:38 Leo10 11:24 Vir12 18:41 Sco11 12:05 Sag11 09:3609 23:5011 16:3810 11:2412 18:4111 12:05
713 18:06 Can12 10:49 Leo14 05:04 Vir13 00:01 Lib15 04:07 Sag13 18:31 Cap13 18:0612 10:4914 05:0413 00:0115 04:0713 18:31
816 04:31 Leo14 23:00 Vir16 17:39 Lib15 11:38 Sco17 11:19 Cap15 22:38 Aqu16 04:3114 23:0016 17:3915 11:3817 11:1915 22:38
918 16:27 Vir17 11:36 Lib19 05:34 Sco17 21:38 Sag19 16:30 Aqu18 01:27 Pis18 16:2717 11:3619 05:3417 21:3819 16:3018 01:27
1021 05:07 Lib19 23:44 Sco21 16:03 Sag20 05:36 Cap21 20:01 Pis20 03:57 Ari21 05:0719 23:4421 16:0320 05:3621 20:0120 03:57
1123 16:54 Sco22 09:53 Sag23 23:57 Cap22 11:05 Aqu23 22:21 Ari22 06:53 Tau23 16:5422 09:5323 23:5722 11:0523 22:2122 06:53
1226 01:50 Sag24 16:32 Cap26 04:25 Aqu24 13:59 Pis26 00:20 Tau24 10:52 Gem26 01:5024 16:3226 04:2524 13:5926 00:2024 10:52
1328 06:47 Cap26 19:16 Aqu28 05:42 Pis26 15:00 Ari28 03:06 Gem26 16:28 Can28 06:4726 19:1628 05:4226 15:0028 03:0626 16:28
1430 08:14 Aqu28 19:05 Pis30 05:13 Ari28 15:34 Tau30 07:55 Can29 00:18 Leo30 08:1428 19:0530 05:1328 15:3430 07:5529 00:18
1530 17:30 Gem   30 17:30  
16183214245275306336
1701 10:42 Vir02 19:08 Sco01 13:48 Sag01 05:56 Cap01 23:06 Pis01 08:22 Ari01 10:4202 19:0801 13:4801 05:5601 23:0601 08:22
1803 22:59 Lib05 06:00 Sag03 22:27 Cap03 12:03 Aqu04 00:27 Ari03 10:01 Tau03 22:5905 06:0003 22:2703 12:0304 00:2703 10:01
1906 11:11 Sco07 13:14 Cap06 02:53 Aqu05 14:22 Pis06 00:25 Tau05 11:27 Gem06 11:1107 13:1406 02:5305 14:2206 00:2505 11:27
2008 21:04 Sag09 16:32 Aqu08 03:47 Pis07 14:07 Ari08 00:50 Gem07 14:04 Can08 21:0409 16:3208 03:4707 14:0708 00:5007 14:04
2111 03:27 Cap11 17:11 Pis10 03:00 Ari09 13:17 Tau10 03:32 Can09 19:18 Leo11 03:2711 17:1110 03:0009 13:1710 03:3209 19:18
2213 06:41 Aqu13 17:09 Ari12 02:45 Tau11 14:02 Gem12 09:48 Leo12 04:03 Vir13 06:4113 17:0912 02:4511 14:0212 09:4812 04:03
2315 08:12 Pis15 18:24 Tau14 04:57 Gem13 18:01 Can14 19:47 Vir14 15:48 Lib15 08:1215 18:2414 04:5713 18:0114 19:4714 15:48
2417 09:38 Ari17 22:14 Gem16 10:39 Can16 01:53 Leo17 08:08 Lib17 04:33 Sco17 09:3817 22:1416 10:3916 01:5317 08:0817 04:33
2519 12:16 Tau20 04:56 Can18 19:38 Leo18 12:51 Vir19 20:48 Sco19 15:53 Sag19 12:1620 04:5618 19:3818 12:5119 20:4819 15:53
2621 16:48 Gem22 14:02 Leo21 06:53 Vir21 01:24 Lib22 08:08 Sag22 00:32 Cap21 16:4822 14:0221 06:5321 01:2422 08:0822 00:32
2723 23:20 Can25 00:57 Vir23 19:19 Lib23 14:04 Sco24 17:25 Cap24 06:34 Aqu23 23:2025 00:5723 19:1923 14:0424 17:2524 06:34
2826 07:49 Leo27 13:12 Lib26 08:06 Sco26 01:50 Sag27 00:30 Aqu26 10:46 Pis26 07:4927 13:1226 08:0626 01:5027 00:3026 10:46
2928 18:17 Vir30 02:03 Sco28 20:10 Sag28 11:47 Cap29 05:23 Pis28 14:02 Ari28 18:1730 02:0328 20:1028 11:4729 05:2328 14:02
3031 06:29 Lib30 19:01 Aqu30 16:55 Tau31 06:29  30 19:01 30 16:55
31
Sheet2
Cell Formulas
RangeFormula
H2:M15,H17:M30H2=IFERROR(LEFT(A2,SEARCH(" ",A2,SEARCH(" ",A2)+1)-1),"")
 
Upvote 0
No, I need all the data in vertical formation , date contains month and year also!
 
Upvote 0
In your previous thread, you used a mix of International and USA style dates and Text that looked like dates.
I assume that you require real dates; you can format them to your preference.
N.B. I made the heading an actual date Jan 1, 1900.
You can format it to your preference.

Astrology.xlsm
AGHI
101-Jan-1900Moon dates
202 21:26 Aqu1900-Jan-02 21:26Aqu
304 22:09 Pis1900-Jan-04 22:09Pis
406 23:45 Ari1900-Jan-06 23:45Ari
509 03:26 Tau1900-Jan-09 03:26Tau
611 09:36 Gem1900-Jan-11 09:36Gem
713 18:06 Can1900-Jan-13 18:06Can
816 04:31 Leo1900-Jan-16 04:31Leo
918 16:27 Vir1900-Jan-18 16:27Vir
1021 05:07 Lib1900-Jan-21 05:07Lib
1123 16:54 Sco1900-Jan-23 16:54Sco
1226 01:50 Sag1900-Jan-26 01:50Sag
1328 06:47 Cap1900-Jan-28 06:47Cap
1430 08:14 Aqu1900-Jan-30 08:14Aqu
1c
Cell Formulas
RangeFormula
A1A1=DATE(0,1,1)
H2:H14H2=$A$1-1+LEFT(A2,2)+MID(A2,4,5)
I2:I14I2=RIGHT(A2,3)
 
Upvote 0
Dave Patton said:
In your previous thread, you used a mix of International and USA style dates and Text that looked like dates.
I assume that you require real dates; you can format them to your preference.
N.B. I made the heading an actual date Jan 1, 1900.
You can format it to your preference.

Astrology.xlsm
AGHI
101-Jan-1900Moon dates
202 21:26 Aqu1900-Jan-02 21:26Aqu
304 22:09 Pis1900-Jan-04 22:09Pis
406 23:45 Ari1900-Jan-06 23:45Ari
509 03:26 Tau1900-Jan-09 03:26Tau
611 09:36 Gem1900-Jan-11 09:36Gem
713 18:06 Can1900-Jan-13 18:06Can
816 04:31 Leo1900-Jan-16 04:31Leo
918 16:27 Vir1900-Jan-18 16:27Vir
1021 05:07 Lib1900-Jan-21 05:07Lib
1123 16:54 Sco1900-Jan-23 16:54Sco
1226 01:50 Sag1900-Jan-26 01:50Sag
1328 06:47 Cap1900-Jan-28 06:47Cap
1430 08:14 Aqu1900-Jan-30 08:14Aqu
1c
Cell Formulas
RangeFormula
A1A1=DATE(0,1,1)
H2:H14H2=$A$1-1+LEFT(A2,2)+MID(A2,4,5)
I2:I14I2=RIGHT(A2,3)
Click to expand...
Hi, thank you for the solution once again !
I have a problem arranging this table, and also with getting the month automatically since it is a very long list 3413 rows and 6 columns and I have to arrange it in a single column for look up
 
Upvote 0
=IFERROR(DATE(YEAR(IF(ISNUMBER(A1),A1,B1)),MONTH(IF(ISNUMBER(A1),A1,B1)),LEFT(A2,2)),"")
 

Attachments

  • fccoc.PNG
    fccoc.PNG
    61.3 KB · Views: 24
Upvote 0
If you do not have a VBA or formula solution to rearrange your data into a single column, you can do it for one six month period at a time with Excel version 365. There may be a way to do it with more rows but I do not know a way to do it. This is the first time that I tried the function.

You can try VStack and experiment with it; there are other related functions that you could try.

You can place the new single column of data wherever you want.

  • Enter the Vstack
  • Enter the formula to extract the date & time and the formula for the sign information
  • Format to your preference
  • Fill or copy down
The formula yields 85 rows. The extract below shows only the first 20 rows.

Astrology.xlsm
ABCDEFGHIJ
101-Jan-190001-Feb-190001-Mar-190001-Apr-190001-May-190001-Jun-19001  
202 21:26 Aqu01 07:48 Pis02 18:03 Ari01 05:01 Tau02 22:24 Can01 15:44 Leo02 21:26 Aqu02-Jan-00 21:26Aqu
304 22:09 Pis03 07:38 Ari04 18:25 Tau03 07:14 Gem05 07:00 Leo04 02:34 Vir04 22:09 Pis04-Jan-00 22:09Pis
406 23:45 Ari05 09:42 Tau06 22:05 Gem05 13:16 Can07 18:35 Vir06 14:59 Lib06 23:45 Ari06-Jan-00 23:45Ari
509 03:26 Tau07 15:07 Gem09 05:45 Can07 23:10 Leo10 07:09 Lib09 02:45 Sco09 03:26 Tau09-Jan-00 03:26Tau
611 09:36 Gem09 23:50 Can11 16:38 Leo10 11:24 Vir12 18:41 Sco11 12:05 Sag11 09:36 Gem11-Jan-00 09:36Gem
713 18:06 Can12 10:49 Leo14 05:04 Vir13 00:01 Lib15 04:07 Sag13 18:31 Cap13 18:06 Can13-Jan-00 18:06Can
816 04:31 Leo14 23:00 Vir16 17:39 Lib15 11:38 Sco17 11:19 Cap15 22:38 Aqu16 04:31 Leo16-Jan-00 04:31Leo
918 16:27 Vir17 11:36 Lib19 05:34 Sco17 21:38 Sag19 16:30 Aqu18 01:27 Pis18 16:27 Vir18-Jan-00 16:27Vir
1021 05:07 Lib19 23:44 Sco21 16:03 Sag20 05:36 Cap21 20:01 Pis20 03:57 Ari21 05:07 Lib21-Jan-00 05:07Lib
1123 16:54 Sco22 09:53 Sag23 23:57 Cap22 11:05 Aqu23 22:21 Ari22 06:53 Tau23 16:54 Sco23-Jan-00 16:54Sco
1226 01:50 Sag24 16:32 Cap26 04:25 Aqu24 13:59 Pis26 00:20 Tau24 10:52 Gem26 01:50 Sag26-Jan-00 01:50Sag
1328 06:47 Cap26 19:16 Aqu28 05:42 Pis26 15:00 Ari28 03:06 Gem26 16:28 Can28 06:47 Cap28-Jan-00 06:47Cap
1430 08:14 Aqu28 19:05 Pis30 05:13 Ari28 15:34 Tau30 07:55 Can29 00:18 Leo30 08:14 Aqu30-Jan-00 08:14Aqu
1530 17:30 Gem32  
1618321424527530633601 07:48 Pis01-Feb-00 07:48Pis
1701 10:42 Vir02 19:08 Sco01 13:48 Sag01 05:56 Cap01 23:06 Pis01 08:22 Ari03 07:38 Ari03-Feb-00 07:38Ari
1803 22:59 Lib05 06:00 Sag03 22:27 Cap03 12:03 Aqu04 00:27 Ari03 10:01 Tau05 09:42 Tau05-Feb-00 09:42Tau
1906 11:11 Sco07 13:14 Cap06 02:53 Aqu05 14:22 Pis06 00:25 Tau05 11:27 Gem07 15:07 Gem07-Feb-00 15:07Gem
2008 21:04 Sag09 16:32 Aqu08 03:47 Pis07 14:07 Ari08 00:50 Gem07 14:04 Can09 23:50 Can09-Feb-00 23:50Can
1cc
Cell Formulas
RangeFormula
A1A1=DATE(0,1,1)
B1B1=DATE(0,2,1)
C1C1=DATE(0,3,1)
D1D1=DATE(0,4,1)
E1E1=DATE(0,5,1)
F1F1=DATE(0,6,1)
H1:H85H1=VSTACK(A1:A14,B1:B14,C1:C14,D1:D15,E1:E14,F1:F14)
I1:I20I1=IF(ISNUMBER(H1),"",MAX($H$1:H1)-1+LEFT(H1,2)+MID(H1,4,5))
J1:J20J1=IF(ISNUMBER(H1),"",RIGHT(H1,3))
Dynamic array formulas.
 
Upvote 0
Solution
It may be possible to do multiple 6 month periods. Revise the formula so that it doesn't yield blanks run the formulas.
Convert the ranges to values and then sort on the date column.
 
Upvote 0
"
It may be possible to do multiple 6 month periods. Revise the formula so that it doesn't yield blanks run the formulas.
Convert the ranges to values and then sort on the date column."

I completed a test with the range that included Jan 1 - Dec 31 1900 and it worked. I do not know how long a range is reasonable.

Test of results
Astrology.xlsm
EF
1
220-12-00 15:00Sag
1e
Cell Formulas
RangeFormula
F2F2=VLOOKUP(E2,B2:C152,2,1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
139
SFCChase
SFCChase
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top