Ask Experts Questions for FREE Help !
Ask
    terra999's Avatar
    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's Avatar
    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's Avatar
    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's Avatar
    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)

    C2: =CHOOSE(LEFT(A2,1)+0, "Internet Lead", "Phone Lead")

    Copy B2:C2 downward.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel 15 digit issue, tried converting to text, text to column feature negates fix [ 6 Answers ]

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....

How to truncate '.' only from the beginning of a string in MS Excel [ 4 Answers ]

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

(Microsoft Excel) - Need to extract a specific number from text string [ 2 Answers ]

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


View more questions Search