View Full Version : Create date from custom text string in excel
terra999
Nov 14, 2013, 08:19 AM
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
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
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
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.