Extract date from a series of numbers

Solved/Closed
Report
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
490
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
5
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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