Excel Tips - Converting a Text String to a Date

  Рет қаралды 122,451

Rob Hambleton

Rob Hambleton

Күн бұрын

Пікірлер: 73
@lescalante86
@lescalante86 Жыл бұрын
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!
@mando116976
@mando116976 2 жыл бұрын
I've seen many videos of this but this was the easiest most helpful one! Thank you!
@denislawton6292
@denislawton6292 2 жыл бұрын
You are the man! CTRL ALT V - mind blown!
@rwfrench66GenX
@rwfrench66GenX 2 жыл бұрын
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.
@kattarhindu8601
@kattarhindu8601 7 жыл бұрын
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
@soulcanyon 7 жыл бұрын
Thanks Karan. I'm happy it helped!
@rajnikanojia438
@rajnikanojia438 2 жыл бұрын
Thank you sir. You Solved Big Problem
@roset3887
@roset3887 2 жыл бұрын
Thank you
@Lerumles
@Lerumles 8 жыл бұрын
You just solved one of my biggest problems
@soulcanyon
@soulcanyon 7 жыл бұрын
Awesome. Thanks for the feedback!
@bhanugupta7543
@bhanugupta7543 2 жыл бұрын
Great video. Thank you for sharing.
@sabrina4902
@sabrina4902 2 жыл бұрын
Thank you!
@soulcanyon
@soulcanyon 2 жыл бұрын
You are welcome!
@dogg91702
@dogg91702 Жыл бұрын
Thank you, this is what I needed❤
@sridharbasavaraju9097
@sridharbasavaraju9097 4 жыл бұрын
Thank you very much, you made my work easy.. though very basic, very useful for beginners in need
@mirjaavad
@mirjaavad 3 жыл бұрын
great video. exactly what i needed. helped a lot. thanks!
@RichieRK7
@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
@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
@tanujagoswami8634
@tanujagoswami8634 3 жыл бұрын
You help me alot today... Thnku soo much #fromIndia ❤️
@baneledludlu7983
@baneledludlu7983 2 жыл бұрын
Thank you very much, this helps a lot, thanks a lot, The Lord bless you!.
@asthanarain1173
@asthanarain1173 3 жыл бұрын
REALLY HELPFUL, THANKS ROB :)
@mohiinit
@mohiinit Жыл бұрын
Very helpful!!! Thank you!!
@bck4589
@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 /
@mauriciosalazar7418
@mauriciosalazar7418 2 жыл бұрын
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
@soulcanyon 2 жыл бұрын
If 20200930 is in cell M26, then: =DATE(LEFT(M26,4),MID(M26,5,2),RIGHT(M26,2))
@somureddy_01
@somureddy_01 3 жыл бұрын
Thank you so much you saved me thanks alot❤️❤️
@jeffrinvite1107
@jeffrinvite1107 Жыл бұрын
Best one!!!!
@BalaMurugan-co1jj
@BalaMurugan-co1jj 2 жыл бұрын
Thanks very much...
@MuhammadAwais-qh2if
@MuhammadAwais-qh2if 4 жыл бұрын
Thanks Dear
@arcotkarthik1452
@arcotkarthik1452 3 жыл бұрын
Very helpful 🙏
@raumgehll
@raumgehll 4 жыл бұрын
MY. MAN. Thank you.
@khushbudesai8126
@khushbudesai8126 4 жыл бұрын
Thank you for this, it helped big time 🙂
@GoosebumpsLoaded
@GoosebumpsLoaded 5 жыл бұрын
you can use simply text function =text(select value,"mm/dd/yy")
@wilmanrahmatnugraha1672
@wilmanrahmatnugraha1672 3 жыл бұрын
how to do it reverse?
@soulcanyon
@soulcanyon 2 жыл бұрын
The point of the video is that I don't want it in text format -- I want it in date format.
@aykumar26
@aykumar26 3 жыл бұрын
Great video
@nuthannuthan479
@nuthannuthan479 3 жыл бұрын
13.01.2021 is in plain format after downloading the file, how to convert this to date format.
@kunalvasita2570
@kunalvasita2570 4 жыл бұрын
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
@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
@steltec
@steltec 8 жыл бұрын
how can you use the 'left' formula if your text string is 71201... meaning 1st of December 2007 ?
@soulcanyon
@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.
@rajeswarip6711
@rajeswarip6711 5 жыл бұрын
Lovely thank you so much for the help.
@martynaskavabata8042
@martynaskavabata8042 7 жыл бұрын
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
@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.
@lungultrasound371
@lungultrasound371 5 жыл бұрын
you've helped me. tx
@muhamadhairol8818
@muhamadhairol8818 3 жыл бұрын
Thank bro. I can convert 2022021 to 2-Feb-21.
@soulcanyon
@soulcanyon 3 жыл бұрын
Yup. You got it?
@jajraj
@jajraj 4 жыл бұрын
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
@soulcanyon 4 жыл бұрын
If 20200218 is in cell D2, then: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2))
@jajraj
@jajraj 4 жыл бұрын
@@soulcanyon thanks
@jyotiloomba464
@jyotiloomba464 4 жыл бұрын
I am using DDMMYY format, I want to convert 051119 to 5/11/2019?
@soulcanyon
@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
@TechGuide4U 4 жыл бұрын
You can check this website - www.juliandate.net/ It will allow you to convert date to YYDDD format
@jyotiloomba464
@jyotiloomba464 4 жыл бұрын
@@soulcanyon thanks brother it's working
@soulcanyon
@soulcanyon 4 жыл бұрын
@@jyotiloomba464 Awesome!
@oddanneout
@oddanneout 4 жыл бұрын
NICE. thank you!
@jskaran5
@jskaran5 7 жыл бұрын
how to change date in string as: 01/05/2009 into " first may two thousand nine. plz. tell me.
@soulcanyon
@soulcanyon 4 жыл бұрын
No idea.
@cynthiazuniga9202
@cynthiazuniga9202 7 жыл бұрын
What can I do if I got 01312015 for 01/31/15
@CurtisWarnasch
@CurtisWarnasch 5 жыл бұрын
Please help, I have year 2000 and this keep failing 20000107 makes the year 1999
@javierfito5077
@javierfito5077 3 жыл бұрын
crap, this doesn´t work for me. they used the day for first number so it has diferent lenght
@FrankLiu-d9r
@FrankLiu-d9r 2 жыл бұрын
how to turn serial hour into date in excel
@mohiinit
@mohiinit Жыл бұрын
For,2009 it's not working
@robinsanvideos7694
@robinsanvideos7694 5 жыл бұрын
Thank you dude
@mahaboobhossain5076
@mahaboobhossain5076 5 жыл бұрын
I NEED THIS FILE BROTHER PLZZZZZZZZZZZZZZZZZZZZZZZZZZ
@utpaldutta3237
@utpaldutta3237 5 жыл бұрын
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 .
@mahaboobhossain5076
@mahaboobhossain5076 5 жыл бұрын
IT IS HELPFUL PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
@rakshithkumar9101
@rakshithkumar9101 5 жыл бұрын
Date (yyyymmdd.000) convert to Date (mm/dd/yyyy) need formula
@soulcanyon
@soulcanyon 2 жыл бұрын
=date(left(value,4),mid(value,5,2),mid(7,2))
@symphonyofdestruction2015
@symphonyofdestruction2015 4 жыл бұрын
What if i have 1402202510215
@alhamdow
@alhamdow 6 жыл бұрын
Very useful! Thanks for sharing!
@Shirowaheire
@Shirowaheire 2 жыл бұрын
Very helpful, thank you!
Excel Won't Sort Dates Correctly - The Solution!
8:28
The Tech Train
Рет қаралды 614 М.
Convert Text to Date Values in Excel - Multiple Examples
9:49
Computergaga
Рет қаралды 309 М.
HELP!!!
00:46
Natan por Aí
Рет қаралды 31 МЛН
World’s strongest WOMAN vs regular GIRLS
00:56
A4
Рет қаралды 11 МЛН
UFC 308 : Уиттакер VS Чимаев
01:54
Setanta Sports UFC
Рет қаралды 893 М.
Microsoft Excel - Convert Text to Dates (complex)
7:02
Sandor Rethy
Рет қаралды 174 М.
Time saving trick: Quick Convert Text to date in Excel
6:06
PK: An Excel Expert
Рет қаралды 105 М.
Excel Shortcuts You SHOULD Know!
8:48
Leila Gharani
Рет қаралды 2,1 МЛН
Discover What XLOOKUP Can Do For YOU (R.I.P. Excel VLOOKUP)
6:59
Leila Gharani
Рет қаралды 1,4 МЛН
Top 15 Advanced Excel 2016 Tips and Tricks
22:07
Sele Training
Рет қаралды 2,6 МЛН
SURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)
5:59
Leila Gharani
Рет қаралды 1,2 МЛН
How To Quickly Convert Text To Dates With Find And Replace In Excel
4:33
Excel Campus - Jon
Рет қаралды 86 М.
HELP!!!
00:46
Natan por Aí
Рет қаралды 31 МЛН