This tip will show you how to convert a string of numbers into a valid Excel Date Format
Жүктеу.....
Пікірлер: 73
@rwfrench66GenX Жыл бұрын
Great tip! Thank you very much! My data had the month in from, day in the middle and year at the end and I had to rearrange the order for my formatting. Also I had 2 digits for the year but I also had 1 and 2 digit months so I had to sort the sheet so I could change where the Mid function started from the left. It was easier to sort the whole sheet and have the dates in numerical order so I could drag the formula down instead of retyping when I found another 2 digit month.
@denislawton62922 жыл бұрын
You are the man! CTRL ALT V - mind blown!
@mando1169762 жыл бұрын
I've seen many videos of this but this was the easiest most helpful one! Thank you!
@lescalante8611 ай бұрын
Thank you so much for this video!! I have been looking for a solution to this for the longest time! the data that I download doesn't translate as a date and I've been having to type in the information for each line. I guess I haven't been searching the right way to change the numbers to a date before, until today and your video popped up! And it worked!!! This is a report we have to pull every day!! Thank you!
@sridharbasavaraju90974 жыл бұрын
Thank you very much, you made my work easy.. though very basic, very useful for beginners in need
@kattarhindu86017 жыл бұрын
thank u rob this was mine situation at audit in a company yesterday with software exporting the date in number format, now m free of worries because of this video.....ty
@soulcanyon
7 жыл бұрын
Thanks Karan. I'm happy it helped!
@mirjaavad3 жыл бұрын
great video. exactly what i needed. helped a lot. thanks!
@alhamdow6 жыл бұрын
Very useful! Thanks for sharing!
@khushbudesai81264 жыл бұрын
Thank you for this, it helped big time 🙂
@rajeswarip67115 жыл бұрын
Lovely thank you so much for the help.
@bhanugupta75432 жыл бұрын
Great video. Thank you for sharing.
@asthanarain11733 жыл бұрын
REALLY HELPFUL, THANKS ROB :)
@roset38872 жыл бұрын
Thank you
@dogg91702 Жыл бұрын
Thank you, this is what I needed❤
@lungultrasound3715 жыл бұрын
you've helped me. tx
@somureddy_013 жыл бұрын
Thank you so much you saved me thanks alot❤️❤️
@Shirowaheire2 жыл бұрын
Very helpful, thank you!
@raumgehll4 жыл бұрын
MY. MAN. Thank you.
@Lerumles8 жыл бұрын
You just solved one of my biggest problems
@soulcanyon
7 жыл бұрын
Awesome. Thanks for the feedback!
@oddanneout4 жыл бұрын
NICE. thank you!
@sabrina49022 жыл бұрын
Thank you!
@soulcanyon
2 жыл бұрын
You are welcome!
@tanujagoswami86343 жыл бұрын
You help me alot today... Thnku soo much #fromIndia ❤️
@rajnikanojia438 Жыл бұрын
Thank you sir. You Solved Big Problem
@jeffrinvite1107 Жыл бұрын
Best one!!!!
@arcotkarthik14523 жыл бұрын
Very helpful 🙏
@baneledludlu7983 Жыл бұрын
Thank you very much, this helps a lot, thanks a lot, The Lord bless you!.
@BalaMurugan-co1jj2 жыл бұрын
Thanks very much...
@MuhammadAwais-qh2if3 жыл бұрын
Thanks Dear
@aykumar263 жыл бұрын
Great video
@GoosebumpsLoaded5 жыл бұрын
you can use simply text function =text(select value,"mm/dd/yy")
@wilmanrahmatnugraha1672
3 жыл бұрын
how to do it reverse?
@soulcanyon
2 жыл бұрын
The point of the video is that I don't want it in text format -- I want it in date format.
@bck4589 Жыл бұрын
Hello sir, in my data first month, mid date and last year how i convert it to day, month and year format? All data was in text format separated by /
@martynaskavabata80427 жыл бұрын
Wicked skills! I was wondering if there is the complete opposite function to this? I was trying to source some sports data from the web but walked into a very nagging problem. For example, I Tried copying soccer results to excel and instead of result 3-1 I get 03:01:00, but when I try to convert it to the number I get the result of 0.13. is there any clever way of going around this? I wouldn't mind to do it individually for one result, but we talking about thousands, which would literally take me forever to convert.
@soulcanyon
7 жыл бұрын
The reason for it being a number value of .13 is that time values are 0-1.0 (a decimal value between 0 and 1). For your problem, use Text to Columns with a delimiter of - on the score. It will put the two parts of the score in separate columns, but then you can just concatenate them again if you want them together.
@steltec8 жыл бұрын
how can you use the 'left' formula if your text string is 71201... meaning 1st of December 2007 ?
@soulcanyon
7 жыл бұрын
Left, Right and Mid just pull whatever you have apart based on where the pieces are. In your situation the first 1 or 2 positions are year, the second 1 or 2 are day (depending on whether there is a 0 or not when the month is single digit -- then you may have to embed Left, Right and Mid into an IF function in those cases. Just use those functions to pull it apart depending on what is where.
@mauriciosalazar74182 жыл бұрын
hi i have this information as text 20200930 and my excersise says " you will need to separate and rejoin the separate parts of the date using an appropriate date function" . I tried with a conctenate(left,mid,right) but is not working . I dont know if I have to put value before al the formula o what am i missing,. Thanks Pd how would you do the same example that you showed but insted of 15 You have 2015
@soulcanyon
2 жыл бұрын
If 20200930 is in cell M26, then: =DATE(LEFT(M26,4),MID(M26,5,2),RIGHT(M26,2))
@nuthannuthan4792 жыл бұрын
13.01.2021 is in plain format after downloading the file, how to convert this to date format.
@robinsanvideos76945 жыл бұрын
Thank you dude
@jskaran56 жыл бұрын
how to change date in string as: 01/05/2009 into " first may two thousand nine. plz. tell me.
@soulcanyon
4 жыл бұрын
No idea.
@cynthiazuniga92027 жыл бұрын
What can I do if I got 01312015 for 01/31/15
@RichieRK7 Жыл бұрын
I know this video is 7 years old but I'm having an issue getting it correct with the full year not just the last 2 digits. I have 20220224 and it showed up with the formula as 08/24/2040 rather than 02/24/2022...Any tips on what I can change in the formula to display correctly?
@soulcanyon
Жыл бұрын
This video is timeless! Say 20220224 is in cell E2. Here is the function: =DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2)). If you are REALLY attached to the leading zero for the month then Format Cells --> Custom Format --> mm/dd/yyyy
@kunalvasita25704 жыл бұрын
can you please explain how we can convert the 03142020 into 03 is month , 14 is day and 2020 is yr ? can you please provide me the formula ?
@soulcanyon
4 жыл бұрын
If A1 contains 03142020, then the formula to convert it would be: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)). Make sure the cell that has this formula is formatted for date mm/dd/yyyy
@muhamadhairol88183 жыл бұрын
Thank bro. I can convert 2022021 to 2-Feb-21.
@soulcanyon
3 жыл бұрын
Yup. You got it?
@jyotiloomba4644 жыл бұрын
I am using DDMMYY format, I want to convert 051119 to 5/11/2019?
@soulcanyon
4 жыл бұрын
If A1 contains 051119, then the formula to convert it would be: =DATE(CONCATENATE(20,RIGHT(A1,2)),LEFT(A1,2),MID(A1,3,2)). Format the cell that contains this formula with Format Cells --> Custom --> dd/mm/yyyy and you will get 11/05/2019
@TechGuide4U
4 жыл бұрын
You can check this website - www.juliandate.net/ It will allow you to convert date to YYDDD format
@jyotiloomba464
4 жыл бұрын
@@soulcanyon thanks brother it's working
@soulcanyon
4 жыл бұрын
@@jyotiloomba464 Awesome!
@mahaboobhossain50765 жыл бұрын
I NEED THIS FILE BROTHER PLZZZZZZZZZZZZZZZZZZZZZZZZZZ
@CurtisWarnasch4 жыл бұрын
Please help, I have year 2000 and this keep failing 20000107 makes the year 1999
@utpaldutta32375 жыл бұрын
How to date format solve text file to excel date format Like DD/MM/YYYY LIKE TEXT FILE DATE 12/06/1967, 1/6/75, 01/6/2023, 25/10/31 .
@user-dg9ks7gd5h2 жыл бұрын
how to turn serial hour into date in excel
@mahaboobhossain50765 жыл бұрын
IT IS HELPFUL PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
@javierfito50773 жыл бұрын
crap, this doesn´t work for me. they used the day for first number so it has diferent lenght
@mohiinit Жыл бұрын
For,2009 it's not working
@jajraj4 жыл бұрын
20200218 plz convert this in date format, left 4 number is year mid 2 is month right 2 num is date, plz help me
@soulcanyon
4 жыл бұрын
If 20200218 is in cell D2, then: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))
@jajraj
4 жыл бұрын
@@soulcanyon thanks
@rakshithkumar91015 жыл бұрын
Date (yyyymmdd.000) convert to Date (mm/dd/yyyy) need formula
Пікірлер: 73
Great tip! Thank you very much! My data had the month in from, day in the middle and year at the end and I had to rearrange the order for my formatting. Also I had 2 digits for the year but I also had 1 and 2 digit months so I had to sort the sheet so I could change where the Mid function started from the left. It was easier to sort the whole sheet and have the dates in numerical order so I could drag the formula down instead of retyping when I found another 2 digit month.
You are the man! CTRL ALT V - mind blown!
I've seen many videos of this but this was the easiest most helpful one! Thank you!
Thank you so much for this video!! I have been looking for a solution to this for the longest time! the data that I download doesn't translate as a date and I've been having to type in the information for each line. I guess I haven't been searching the right way to change the numbers to a date before, until today and your video popped up! And it worked!!! This is a report we have to pull every day!! Thank you!
Thank you very much, you made my work easy.. though very basic, very useful for beginners in need
thank u rob this was mine situation at audit in a company yesterday with software exporting the date in number format, now m free of worries because of this video.....ty
@soulcanyon
7 жыл бұрын
Thanks Karan. I'm happy it helped!
great video. exactly what i needed. helped a lot. thanks!
Very useful! Thanks for sharing!
Thank you for this, it helped big time 🙂
Lovely thank you so much for the help.
Great video. Thank you for sharing.
REALLY HELPFUL, THANKS ROB :)
Thank you
Thank you, this is what I needed❤
you've helped me. tx
Thank you so much you saved me thanks alot❤️❤️
Very helpful, thank you!
MY. MAN. Thank you.
You just solved one of my biggest problems
@soulcanyon
7 жыл бұрын
Awesome. Thanks for the feedback!
NICE. thank you!
Thank you!
@soulcanyon
2 жыл бұрын
You are welcome!
You help me alot today... Thnku soo much #fromIndia ❤️
Thank you sir. You Solved Big Problem
Best one!!!!
Very helpful 🙏
Thank you very much, this helps a lot, thanks a lot, The Lord bless you!.
Thanks very much...
Thanks Dear
Great video
you can use simply text function =text(select value,"mm/dd/yy")
@wilmanrahmatnugraha1672
3 жыл бұрын
how to do it reverse?
@soulcanyon
2 жыл бұрын
The point of the video is that I don't want it in text format -- I want it in date format.
Hello sir, in my data first month, mid date and last year how i convert it to day, month and year format? All data was in text format separated by /
Wicked skills! I was wondering if there is the complete opposite function to this? I was trying to source some sports data from the web but walked into a very nagging problem. For example, I Tried copying soccer results to excel and instead of result 3-1 I get 03:01:00, but when I try to convert it to the number I get the result of 0.13. is there any clever way of going around this? I wouldn't mind to do it individually for one result, but we talking about thousands, which would literally take me forever to convert.
@soulcanyon
7 жыл бұрын
The reason for it being a number value of .13 is that time values are 0-1.0 (a decimal value between 0 and 1). For your problem, use Text to Columns with a delimiter of - on the score. It will put the two parts of the score in separate columns, but then you can just concatenate them again if you want them together.
how can you use the 'left' formula if your text string is 71201... meaning 1st of December 2007 ?
@soulcanyon
7 жыл бұрын
Left, Right and Mid just pull whatever you have apart based on where the pieces are. In your situation the first 1 or 2 positions are year, the second 1 or 2 are day (depending on whether there is a 0 or not when the month is single digit -- then you may have to embed Left, Right and Mid into an IF function in those cases. Just use those functions to pull it apart depending on what is where.
hi i have this information as text 20200930 and my excersise says " you will need to separate and rejoin the separate parts of the date using an appropriate date function" . I tried with a conctenate(left,mid,right) but is not working . I dont know if I have to put value before al the formula o what am i missing,. Thanks Pd how would you do the same example that you showed but insted of 15 You have 2015
@soulcanyon
2 жыл бұрын
If 20200930 is in cell M26, then: =DATE(LEFT(M26,4),MID(M26,5,2),RIGHT(M26,2))
13.01.2021 is in plain format after downloading the file, how to convert this to date format.
Thank you dude
how to change date in string as: 01/05/2009 into " first may two thousand nine. plz. tell me.
@soulcanyon
4 жыл бұрын
No idea.
What can I do if I got 01312015 for 01/31/15
I know this video is 7 years old but I'm having an issue getting it correct with the full year not just the last 2 digits. I have 20220224 and it showed up with the formula as 08/24/2040 rather than 02/24/2022...Any tips on what I can change in the formula to display correctly?
@soulcanyon
Жыл бұрын
This video is timeless! Say 20220224 is in cell E2. Here is the function: =DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2)). If you are REALLY attached to the leading zero for the month then Format Cells --> Custom Format --> mm/dd/yyyy
can you please explain how we can convert the 03142020 into 03 is month , 14 is day and 2020 is yr ? can you please provide me the formula ?
@soulcanyon
4 жыл бұрын
If A1 contains 03142020, then the formula to convert it would be: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)). Make sure the cell that has this formula is formatted for date mm/dd/yyyy
Thank bro. I can convert 2022021 to 2-Feb-21.
@soulcanyon
3 жыл бұрын
Yup. You got it?
I am using DDMMYY format, I want to convert 051119 to 5/11/2019?
@soulcanyon
4 жыл бұрын
If A1 contains 051119, then the formula to convert it would be: =DATE(CONCATENATE(20,RIGHT(A1,2)),LEFT(A1,2),MID(A1,3,2)). Format the cell that contains this formula with Format Cells --> Custom --> dd/mm/yyyy and you will get 11/05/2019
@TechGuide4U
4 жыл бұрын
You can check this website - www.juliandate.net/ It will allow you to convert date to YYDDD format
@jyotiloomba464
4 жыл бұрын
@@soulcanyon thanks brother it's working
@soulcanyon
4 жыл бұрын
@@jyotiloomba464 Awesome!
I NEED THIS FILE BROTHER PLZZZZZZZZZZZZZZZZZZZZZZZZZZ
Please help, I have year 2000 and this keep failing 20000107 makes the year 1999
How to date format solve text file to excel date format Like DD/MM/YYYY LIKE TEXT FILE DATE 12/06/1967, 1/6/75, 01/6/2023, 25/10/31 .
how to turn serial hour into date in excel
IT IS HELPFUL PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
crap, this doesn´t work for me. they used the day for first number so it has diferent lenght
For,2009 it's not working
20200218 plz convert this in date format, left 4 number is year mid 2 is month right 2 num is date, plz help me
@soulcanyon
4 жыл бұрын
If 20200218 is in cell D2, then: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))
@jajraj
4 жыл бұрын
@@soulcanyon thanks
Date (yyyymmdd.000) convert to Date (mm/dd/yyyy) need formula
@soulcanyon
2 жыл бұрын
=date(left(value,4),mid(value,5,2),mid(7,2))
What if i have 1402202510215
Very helpful!!! Thank you!!