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!
@mando1169762 жыл бұрын
I've seen many videos of this but this was the easiest most helpful one! Thank you!
@denislawton62922 жыл бұрын
You are the man! CTRL ALT V - mind blown!
@rwfrench66GenX2 жыл бұрын
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.
@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
@soulcanyon7 жыл бұрын
Thanks Karan. I'm happy it helped!
@rajnikanojia4382 жыл бұрын
Thank you sir. You Solved Big Problem
@roset38872 жыл бұрын
Thank you
@Lerumles8 жыл бұрын
You just solved one of my biggest problems
@soulcanyon7 жыл бұрын
Awesome. Thanks for the feedback!
@bhanugupta75432 жыл бұрын
Great video. Thank you for sharing.
@sabrina49022 жыл бұрын
Thank you!
@soulcanyon2 жыл бұрын
You are welcome!
@dogg91702 Жыл бұрын
Thank you, this is what I needed❤
@sridharbasavaraju90974 жыл бұрын
Thank you very much, you made my work easy.. though very basic, very useful for beginners in need
@mirjaavad3 жыл бұрын
great video. exactly what i needed. helped a lot. thanks!
@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
@tanujagoswami86343 жыл бұрын
You help me alot today... Thnku soo much #fromIndia ❤️
@baneledludlu79832 жыл бұрын
Thank you very much, this helps a lot, thanks a lot, The Lord bless you!.
@asthanarain11733 жыл бұрын
REALLY HELPFUL, THANKS ROB :)
@mohiinit Жыл бұрын
Very helpful!!! Thank you!!
@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 /
@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
@soulcanyon2 жыл бұрын
If 20200930 is in cell M26, then: =DATE(LEFT(M26,4),MID(M26,5,2),RIGHT(M26,2))
@somureddy_013 жыл бұрын
Thank you so much you saved me thanks alot❤️❤️
@jeffrinvite1107 Жыл бұрын
Best one!!!!
@BalaMurugan-co1jj2 жыл бұрын
Thanks very much...
@MuhammadAwais-qh2if4 жыл бұрын
Thanks Dear
@arcotkarthik14523 жыл бұрын
Very helpful 🙏
@raumgehll4 жыл бұрын
MY. MAN. Thank you.
@khushbudesai81264 жыл бұрын
Thank you for this, it helped big time 🙂
@GoosebumpsLoaded5 жыл бұрын
you can use simply text function =text(select value,"mm/dd/yy")
@wilmanrahmatnugraha16723 жыл бұрын
how to do it reverse?
@soulcanyon2 жыл бұрын
The point of the video is that I don't want it in text format -- I want it in date format.
@aykumar263 жыл бұрын
Great video
@nuthannuthan4793 жыл бұрын
13.01.2021 is in plain format after downloading the file, how to convert this to date format.
@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 ?
@soulcanyon4 жыл бұрын
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
@steltec8 жыл бұрын
how can you use the 'left' formula if your text string is 71201... meaning 1st of December 2007 ?
@soulcanyon7 жыл бұрын
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.
@rajeswarip67115 жыл бұрын
Lovely thank you so much for the help.
@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.
@soulcanyon7 жыл бұрын
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.
@lungultrasound3715 жыл бұрын
you've helped me. tx
@muhamadhairol88183 жыл бұрын
Thank bro. I can convert 2022021 to 2-Feb-21.
@soulcanyon3 жыл бұрын
Yup. You got it?
@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
@soulcanyon4 жыл бұрын
If 20200218 is in cell D2, then: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))
@jajraj4 жыл бұрын
@@soulcanyon thanks
@jyotiloomba4644 жыл бұрын
I am using DDMMYY format, I want to convert 051119 to 5/11/2019?
@soulcanyon4 жыл бұрын
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
@TechGuide4U4 жыл бұрын
You can check this website - www.juliandate.net/ It will allow you to convert date to YYDDD format
@jyotiloomba4644 жыл бұрын
@@soulcanyon thanks brother it's working
@soulcanyon4 жыл бұрын
@@jyotiloomba464 Awesome!
@oddanneout4 жыл бұрын
NICE. thank you!
@jskaran57 жыл бұрын
how to change date in string as: 01/05/2009 into " first may two thousand nine. plz. tell me.
@soulcanyon4 жыл бұрын
No idea.
@cynthiazuniga92027 жыл бұрын
What can I do if I got 01312015 for 01/31/15
@CurtisWarnasch5 жыл бұрын
Please help, I have year 2000 and this keep failing 20000107 makes the year 1999
@javierfito50773 жыл бұрын
crap, this doesn´t work for me. they used the day for first number so it has diferent lenght
@FrankLiu-d9r2 жыл бұрын
how to turn serial hour into date in excel
@mohiinit Жыл бұрын
For,2009 it's not working
@robinsanvideos76945 жыл бұрын
Thank you dude
@mahaboobhossain50765 жыл бұрын
I NEED THIS FILE BROTHER PLZZZZZZZZZZZZZZZZZZZZZZZZZZ
@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 .
@mahaboobhossain50765 жыл бұрын
IT IS HELPFUL PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
@rakshithkumar91015 жыл бұрын
Date (yyyymmdd.000) convert to Date (mm/dd/yyyy) need formula