terra999 Posts: 2, Reputation: 1 New Member #1 Nov 14, 2013, 08:19 AM
Create date from custom text string in excel
I need to convert a custom text to date in excel

If custom number is 137910O39

1st 1 = Internet 2 = Phone
2nd Year 3 = 13 and 4 = 14 etc
3rd Month ID 7 = 0 and 8 = 1
4th Month ID2 1 - 9 w/ 7 in 3rd place = Jan - Sept
0 - 2 w/ 8 in 3rd place = Oct - Dec
5th Day ID - 01 through 31
6th
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #2 Nov 14, 2013, 08:26 AM
Provide an example text string and the resulting date that would be derived from it, provide one for EVERY month of the year and include several years as well. Show us all the variations. I can't really follow the logic from what is given so far.
 terra999 Posts: 2, Reputation: 1 New Member #3 Nov 14, 2013, 09:43 AM
Thank you for the reply, hope this makes it clear

1st digit determines if it was Internet lead (1) or Phone (2)
2nd digit determines year 3 = 2013 and 4 = 2014 etc
3rd digit reads first 1st digit of 2 digit month where 7 = 0 and 8 = 1
4th digit reads 2nd digit of 2 digit month – so a 7 followed by 1-9 is Jan to Sept and 8 followed by 0-2 is Oct – Dec
5th and 6th digit are the 2 digit day of the month so 01 is 1st day and 25 is the 25th day

137105P39 would equal 01/05/2013 and was an Internet lead
237105P39 would equal 01/05/2013 and was an Phone lead
137205P39 would equal 02/05/2013 and was an Internet lead
237205P39 would equal 02/05/2013 and was an Phone lead
137305P39 would equal 03/05/2013 and was an Internet lead
237305P39 would equal 03/05/2013 and was an Phone lead
137405P39 would equal 04/05/2013 and was an Internet lead
237405P39 would equal 04/05/2013 and was an Phone lead
137505P39 would equal 05/05/2013 and was an Internet lead
237505P39 would equal 05/05/2013 and was an Phone lead
137605P39 would equal 06/05/2013 and was an Internet lead
237605P39 would equal 06/05/2013 and was an Phone lead
137705P39 would equal 07/05/2013 and was an Internet lead
237705P39 would equal 07/05/2013 and was an Phone lead
137805P39 would equal 08/05/2013 and was an Internet lead
237805P39 would equal 08/05/2013 and was an Phone lead
137905P39 would equal 09/05/2013 and was an Internet lead
237905P39 would equal 09/05/2013 and was an Phone lead
138005P39 would equal 10/05/2013 and was an Internet lead
238005P39 would equal 10/05/2013 and was an Phone lead
138105P39 would equal 11/05/2013 and was an Internet lead
238105P39 would equal 11/05/2013 and was an Phone lead
138205P39 would equal 12/05/2013 and was an Internet lead
238205P39 would equal 12/05/2013 and was an Phone lead

147105P39 would equal 01/05/2014 and was an Internet lead
247105P39 would equal 01/05/2014 and was an Phone lead
147205P39 would equal 02/05/2014 and was an Internet lead
247205P39 would equal 02/05/2014 and was an Phone lead
147305P39 would equal 03/05/2014 and was an Internet lead
247305P39 would equal 03/05/2014 and was an Phone lead
147405P39 would equal 04/05/2014 and was an Internet lead
247405P39 would equal 04/05/2014 and was an Phone lead
147505P39 would equal 05/05/2014 and was an Internet lead
247505P39 would equal 05/05/2014 and was an Phone lead
147605P39 would equal 06/05/2014 and was an Internet lead
247605P39 would equal 06/05/2014 and was an Phone lead
147705P39 would equal 07/05/2014 and was an Internet lead
247705P39 would equal 07/05/2014 and was an Phone lead
147805P39 would equal 08/05/2014 and was an Internet lead
247805P39 would equal 08/05/2014 and was an Phone lead
147905P39 would equal 09/05/2014 and was an Internet lead
247905P39 would equal 09/05/2014 and was an Phone lead
148005P39 would equal 10/05/2014 and was an Internet lead
248005P39 would equal 10/05/2014 and was an Phone lead
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #4 Nov 14, 2013, 02:11 PM
Assuming those text strings are in A2 going down:

B2: =DATEVALUE(MID(A2,3,2)-70 & "/" & MID(A2,5,2) & "/" & CHOOSE(MID(A2,2,1)+0,,, 2013, 2014))
(format cell as Date)

Copy B2:C2 downward.

 Question Tools Search this Question Search this Question: Advanced Search

## Check out some similar questions!

I have the following numbers that exceed 15 characters that needs to be split into its own columns. Down the road, there would be thousands of such rows of data with the first couple set of unique numbers. 890432453253208820,5004500558,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11...

How can I create my own custom vehicle magnets? [ 0 Answers ]

You can customize your own magnets for your vehicles by following these simple tips: Use legible font. Easy-to-read text is your best bet for any type of vehicle magnet. If your readers have to strain their eyes just to see your design, it might not get your message across in a clear manner....

I am generating an output in MS Excel . Only in one column I want to truncate '.' from beginning. If it is not starting with then leave it. Example : Input Col 1 .06R215.23 .02145.HS 12HJ458 12.232.32

For example, cell K1 looks like this Z. Thornton(90), J. Bornstein(90), A. Jazic(65), C. Talley(90), Y. Cuesta(90), S. Kljestan(90), P. Nagamura(90), M. Lahoud(90), J. Braun(90), J. Padilla(46), Maicon Santos(60) I want a formula that will give me the number to the right of a name if the cell...

How to create a custom dictionary [ 1 Answers ]

Hi, I am an assistant professor and I am working on a new method of learning English or any other languages. I have a word list of 1000 words. Like this: A (an) Able About Account Acid Across