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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 5, 2010 at 06:45 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 5, 2010 at 06:45 AM
Related:
- Extract date from a series of numbers
- Best scandinavian crime series on netflix - Guide
- Audio track download for series - Guide
- Apple watch series 9 release date - Guide
- Hitman 3 cheats xbox series x - Guide
- Xbox series x 3d blu ray - Guide
3 responses
aquarelle
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
May 5, 2010 at 06:30 AM
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 5, 2010 at 06:30 AM
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))
=DATE(MID(A1,5,4), MID(A1,3,2),LEFT(A1,2))
seekermay
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
5
May 5, 2010 at 06:39 AM
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
where 04(month)25(date)2010(year)
then how would this be??
Thanks...seekermay
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 5, 2010 at 06:45 AM
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
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
