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!
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.
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
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?
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
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
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.
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.
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.
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
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 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!
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!
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.
Great video. Thank you for sharing.
Thank you, this is what I needed❤
REALLY HELPFUL, THANKS ROB :)
great video. exactly what i needed. helped a lot. thanks!
You just solved one of my biggest problems
Awesome. Thanks for the feedback!
You help me alot today... Thnku soo much #fromIndia ❤️
Thank you so much you saved me thanks alot❤️❤️
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
Thanks Karan. I'm happy it helped!
Thank you sir. You Solved Big Problem
Thank you very much, you made my work easy.. though very basic, very useful for beginners in need
Very helpful 🙏
Very useful! Thanks for sharing!
Very helpful, thank you!
MY. MAN. Thank you.
Thank you very much, this helps a lot, thanks a lot, The Lord bless you!.
Thank you for this, it helped big time 🙂
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?
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
you can use simply text function =text(select value,"mm/dd/yy")
how to do it reverse?
The point of the video is that I don't want it in text format -- I want it in date format.
you've helped me. tx
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
If 20200930 is in cell M26, then: =DATE(LEFT(M26,4),MID(M26,5,2),RIGHT(M26,2))
Thank you!
You are welcome!
Best one!!!!
Great video
Thank you
Lovely thank you so much for the help.
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.
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.
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 /
how can you use the 'left' formula if your text string is 71201... meaning 1st of December 2007 ?
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.
NICE. thank you!
Thanks very much...
Thanks Dear
13.01.2021 is in plain format after downloading the file, how to convert this to date format.
I am using DDMMYY format, I want to convert 051119 to 5/11/2019?
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
You can check this website - www.juliandate.net/
It will allow you to convert date to YYDDD format
@@soulcanyon thanks brother it's working
@@jyotiloomba464 Awesome!
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 ?
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
how to change date in string as:
01/05/2009 into " first may two thousand nine.
plz. tell me.
No idea.
What can I do if I got 01312015 for 01/31/15
20200218
plz convert this in date format, left 4 number is year mid 2 is month right 2 num is date, plz help me
If 20200218 is in cell D2, then: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))
@@soulcanyon thanks
Please help, I have year 2000 and this keep failing 20000107 makes the year 1999
Thank bro. I can convert 2022021 to 2-Feb-21.
Yup. You got it?
how to turn serial hour into date in excel
I NEED THIS FILE BROTHER PLZZZZZZZZZZZZZZZZZZZZZZZZZZ
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
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 .
Thank you dude
IT IS HELPFUL PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
What if i have 1402202510215
Date (yyyymmdd.000) convert to Date (mm/dd/yyyy) need formula
=date(left(value,4),mid(value,5,2),mid(7,2))
Very helpful!!! Thank you!!