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
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.
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
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 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
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 are the man! CTRL ALT V - mind blown!
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!
I've seen many videos of this but this was the easiest most helpful one! Thank you!
great video. exactly what i needed. helped a lot. thanks!
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.
Very useful! Thanks for sharing!
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
Thanks Karan. I'm happy it helped!
REALLY HELPFUL, THANKS ROB :)
Great video. Thank you for sharing.
Thank you for this, it helped big time 🙂
Lovely thank you so much for the help.
Thank you, this is what I needed❤
Very helpful, thank you!
You just solved one of my biggest problems
Awesome. Thanks for the feedback!
you've helped me. tx
MY. MAN. Thank you.
Thank you so much you saved me thanks alot❤️❤️
Thank you sir. You Solved Big Problem
NICE. thank you!
Very helpful 🙏
You help me alot today... Thnku soo much #fromIndia ❤️
Best one!!!!
Thank you
Thanks very much...
Great video
Thanks Dear
Thank you!
You are welcome!
Thank you very much, this helps a lot, thanks a lot, The Lord bless you!.
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 /
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))
13.01.2021 is in plain format after downloading the file, how to convert this to date format.
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.
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.
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
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.
What can I do if I got 01312015 for 01/31/15
how to change date in string as:
01/05/2009 into " first may two thousand nine.
plz. tell me.
No idea.
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
Please help, I have year 2000 and this keep failing 20000107 makes the year 1999
Thank you dude
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!
Thank bro. I can convert 2022021 to 2-Feb-21.
Yup. You got it?
crap, this doesn´t work for me. they used the day for first number so it has diferent lenght
I NEED THIS FILE BROTHER PLZZZZZZZZZZZZZZZZZZZZZZZZZZ
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 .
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
IT IS HELPFUL PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
how to turn serial hour into date in excel
For,2009 it's not working
Date (yyyymmdd.000) convert to Date (mm/dd/yyyy) need formula
=date(left(value,4),mid(value,5,2),mid(7,2))
What if i have 1402202510215
Very helpful!!! Thank you!!