Extract date from a series of numbers

Solved/Closed
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 - May 5, 2010 at 06:15 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 5, 2010 at 06:45 AM
Hi,

I need to extract date from a series of numbers like 250420102356478
where 25(date)04(month)2010(year)2056478(sequence number)
and answer should appear in another cell as 04/25/2010

Pls help me out.

Thanks.....seekermay

3 replies

aquarelle Posts 7118 Registration date Saturday April 7, 2007 Status Moderator Last seen July 29, 2022 491
May 5, 2010 at 06:30 AM
Hi,

I supposed the 250420102356478 number was in A2, then to extract it as you want in B2 (for exemple), you should try with this formula :

=MID(LEFT(A2,8),1,2)&"/"&MID(LEFT(A2,8),3,2)&"/"&MID(LEFT(A2,8),5,4)

Best regards
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 5, 2010 at 06:30 AM
If this number is in cell A1 you can use this formula

=DATE(MID(A1,5,4), MID(A1,3,2),LEFT(A1,2))
0
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 5
May 5, 2010 at 06:39 AM
thanks, rizvisa1. it works great but i want to know the logic on which this formula identify the numerics as month, date and year. for example if series is 042520102356478

where 04(month)25(date)2010(year)

then how would this be??

Thanks...seekermay
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 5, 2010 at 06:45 AM
ok
We are using date function. Date function needs three variable, year, month and day
so we extract those parts
Since year and month are in middle we use mid to extract them and left to extract day


mid(text, start position, number of characters to be extracted)
left(text, number of characters to be extracted)

so for year
MID(A1,5,4)

for month
MID(A1,3,2)

and for day
LEFT(A1,2)

hope it is clear now
0