Convert Text to Date Values in Excel - Multiple Examples
Тәжірибелік нұсқаулар және стиль
In this video, we will demonstrate a variety of techniques to convert text to date values in Excel. These will include formulas and also good old classic Excel tips.
Read the full article I wrote at HowTo Geek - www.howtogeek.com/415246/how-...
Analysing data using dates is very commonplace. But if Excel does not recognise the values as dates, then its all useless.
This video walks through different scenarios and their solutions to convert the text to date.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
Пікірлер: 228
You have made some of the best Excel videos I've ever seen. Please keep them coming, although, I am only about 3 or 4 videos into your library of tutorials. Helping me no end with my day job :) All the best Sir.
@Computergaga
4 жыл бұрын
Thank you very much. Your comment is much appreciated.
That is stunning. Thank you so much Alan. I appreciate your efforts to make such great videos.
@Computergaga
5 жыл бұрын
Thank you Salim. You're very welcome.
Hi Alan.. another great lesson. Thanks for the conversion tips and tricks. Gaga is always full of useful and informative information. Thumbs up!
@Computergaga
5 жыл бұрын
Thank you Wayne.
Amazing. The internet is sweet but your presentation is sweeter. I am giving you lots of hugs of appreciation for making complicated life very simple. You are the best Alan. Thank you.
@Computergaga
2 жыл бұрын
Thanks, Mel. I'm happy to hear that it helped.
Yes, very useful, I think we can use flash fill too to resolve the problem just write 12/02/2010 near 12.02.2010 for example, and use flash fill for the rest
@Computergaga
5 жыл бұрын
Thank you.
Alan, thank you so much you save my nerves and time to replace COMA in LONG list of Date data. Regards Alexander.
@Computergaga
2 жыл бұрын
Glad to help, Alexander 👍
Thank you so much. It very useful for me while working on text date big data.
@Computergaga
2 жыл бұрын
You're welcome, Divya. Great to hear that the video helped.
Thank you! it's very helpful to me, too much useful.
@Computergaga
3 жыл бұрын
Great to hear Sakawat 👍
Thank you! This was the exact answer I was looking for :)
@Computergaga
4 ай бұрын
Glad it was helpful 🙂
Very cool, good tips, it will help a lot. Thank you
@Computergaga
5 жыл бұрын
You're welcome.
Thank you! it's very helpful to me.
@Computergaga
4 жыл бұрын
Great to hear. Happy to help Patil.
thanks for this- it helped me a lot!
Very Thankful to you Sir
@Computergaga
Жыл бұрын
No worries 👍
Fantastic. Thank You
@Computergaga
4 жыл бұрын
You're welcome Joshua. Thank you.
Great video! Thank you very much!!!!!!
@Computergaga
3 жыл бұрын
You're welcome, Joanne. Thanks.
Find and replace proved helpful. Thanks.
@Computergaga
3 ай бұрын
You're welcome!
Very clear and just what I needed. Thank you
@Computergaga
3 ай бұрын
Glad it was helpful!
You just solved a headache for me... thank you!! you deserve a like and subscribe for this for sure!!...
@Computergaga
Жыл бұрын
Thank you very much. Happy to have helped.
Thankyou so much for the technique. You really helped me today :)
@Computergaga
11 ай бұрын
You are so welcome! Happy to help.
Thank you! You saved me a lot of frustration!
@Computergaga
3 жыл бұрын
Excellent! That pleases me.
Excellent, solved all doubts. Thank you
@Computergaga
Жыл бұрын
Great! Thank you.
STUNNING!
@Computergaga
3 жыл бұрын
Thank you 😊
Very helpful Thank you mate
@Computergaga
Жыл бұрын
No problem 👍
Poli sadanam , Thanks
@Computergaga
2 жыл бұрын
My pleasure, Ganesh.
THANK YOUUUU!
@Computergaga
Жыл бұрын
You're welcome.
Thank you. It worked
@Computergaga
2 жыл бұрын
You're welcome!
Its really helpful video for me God bless you you solve my problem
@Computergaga
4 жыл бұрын
Great to hear. You're welcome 😀
this is what I needed today. thank you so much.. yes its useful.
@Computergaga
Жыл бұрын
Great! You're so welcome!
Thanks well explained.
@Computergaga
3 жыл бұрын
Thank you, Ishtiaq.
Thank you so much
@Computergaga
2 жыл бұрын
You're welcome 😊
THNX A LOT I WANTED TO REPLACE 2021 TO 2022 SO UR VIDEO HELPED ME A LOT THNX ONCE AGAIN
@Computergaga
Жыл бұрын
You're welcome!
Fantastic man, Thank you
@Computergaga
2 жыл бұрын
You're welcome. Thank you, Hadi.
Thank you. You saved me a lot of time.
@Computergaga
Ай бұрын
Great to hear. You are welcome.
Thank you. This is very useful :)
@Computergaga
2 жыл бұрын
Great to hear. You're welcome.
really so useful thanks for sharing ^^
@Computergaga
3 жыл бұрын
You're welcome 😊
It works thankyou👍
@Computergaga
3 жыл бұрын
No problem 👍
Thank You
@Computergaga
Жыл бұрын
You're welcome!
Thank you
@Computergaga
3 жыл бұрын
You're welcome, Arsipku.
Thanks so much!!!!
@Computergaga
3 жыл бұрын
You're very welcome 👍
T’hank you very much
@Computergaga
3 жыл бұрын
You're welcome 👍
Thank you..
@Computergaga
2 жыл бұрын
You're welcome 😁
Thank you, thank you, THANK YOU!!!!!!
@Computergaga
3 жыл бұрын
You're welcome 👍
Thanks sir.
@Computergaga
5 жыл бұрын
My pleasure Babar.
loved it
@Computergaga
2 жыл бұрын
😊
thanks for sharing date fun.
@Computergaga
5 жыл бұрын
My pleasure. It is fun 😃
Thanks a lot..!!
@Computergaga
3 жыл бұрын
Welcome 😊
Thank you so much sir
@Computergaga
3 жыл бұрын
You're welcome 👍
thanks just what i need to know
@Computergaga
Жыл бұрын
Awesome! You're welcome.
Great Computer Gaga!
@Computergaga
3 жыл бұрын
Thank you, Shanmuga.
Thanks
@Computergaga
3 жыл бұрын
You're welcome, Arvind.
it is unbelievable sir really great ideas you served up easily.
@Computergaga
4 жыл бұрын
Excellent! Thank you, Umesh.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
thankx alot dear your video helped me alot
@Computergaga
3 жыл бұрын
Most welcome, Khawar 😊
thank you
@Computergaga
3 ай бұрын
You're welcome
tq so much
@Computergaga
Жыл бұрын
You're very welcome, Naren.
thank you sir
@Computergaga
4 жыл бұрын
You're welcome Vikash.
thanks, you made my life easy
@Computergaga
3 жыл бұрын
Good to hear. No worries 👍
My problem is resolved,Thank you so much
@Computergaga
2 жыл бұрын
You're very welcome.
Thank you bro (visitor from INDIA )
@Computergaga
2 жыл бұрын
No worries 👍
REALLY ITS HELPFULL FOR US THANKS
@Computergaga
2 жыл бұрын
Great! You're welcome.
Really helpful
@Computergaga
3 жыл бұрын
Thank you, Nishad.
Any suggestions to covert for example: 1m 30s to 1.5 or 4m 30s to 4.5 ???? Please
Awesome formula....
@Computergaga
3 жыл бұрын
Thank you, Malise.
Great video
@Computergaga
3 жыл бұрын
Thank you, Anil.
Thank you very much ^_^
@Computergaga
2 жыл бұрын
No problem 😊
Saved lots of time tq man 😍
@Computergaga
2 жыл бұрын
No problem 👍
Expained as if I was a 4 year old. Just what I needed, thank you!
@Computergaga
3 жыл бұрын
😆 You're welcome Juan.
awesome
@Computergaga
3 жыл бұрын
Thank you 😊
Hello, region where I am in we follow month/day/year, how do I change that with the formula..
useful
@Computergaga
Жыл бұрын
Thank you.
Nice
@Computergaga
2 жыл бұрын
Thank you, Almamun.
You're an Angel I needed desperately. Wow! Thank you very much!
@Computergaga
3 жыл бұрын
You are so welcome
WOW ♥
@Computergaga
5 жыл бұрын
Thank you Hazem.
i got what i wanted in this video.
@Computergaga
3 жыл бұрын
Great to hear 👍
I have a spreadsheet at work with a column listing training expiry dates. I need to use the set icon traffic light system. I want the expired dates to be red. Six months before the due date they need to be amber and one year before expiry date they can remain green. Please can anyone help I have been trying for a few hours and am flummoxed. Thank you Julie
@abhiseksingh4886
3 жыл бұрын
U can use conditional formatting. There is an option of dates greater than less than kinda. u can watch a channel viz "off to office" to ward off ur confusions.
Hi, I've just seen your video and I was wondering if you could advise how I would convert this text example string "January 7, 2022" into an actual date in UK format (ignore quotations) - thanks in advance!
@mrteacup8781
2 жыл бұрын
skip to 3:21 this works
@david_wright
2 жыл бұрын
@@mrteacup8781 Thanks pal, I’ll check it out 😃👍🏼
@Computergaga
2 жыл бұрын
I hope it worked for you David.
Helped be sort an issue I had. Thank you
@Computergaga
3 жыл бұрын
Great! Happy to help.
@Computergaga thanks for a nice tutorial! I appreciate that you're still responding to messages here, even though your tutorial is 2+ years old! I need your urgent help. I converted a very important .db database file to csv format using a SQL a convertor tool, and all the dates (and time) in the output file are showing as 13 digit numbers. Thereafter, I saved the exported / output .csv file as an Excel file. How do I convert these numbers back to a ddmmYYY date fomat in Excel? (I am in the GST +5.30 hours Time Zone.)
@Computergaga
2 жыл бұрын
Sorry for the delayed response on this. I've just seen it while replying to another comment. I hope you fixed the data Ramnik.
@ramnik.khanna
2 жыл бұрын
@@Computergaga , yes thanks! I was able to resolve it.
Very nice video but when I use =Value () function on a date column in dd/mm/yyyy format, it still throws a #value error in ones that have text formatted dates.
@Computergaga
2 жыл бұрын
I would need to see it to explain in adequate detail, but essentially Excel cannot recognise the number. It is not in a format recognised by your locale formatting. Text to Columns is a better approach for situations like this, or even the DATE function and give it the three parts using text functions.
@crazydrifter13
2 жыл бұрын
@@Computergaga thank you so much for your reply. I eventually fixed it by changing "region and language" in windows to US English. US English has those slash type date formats and English India did not have them so it was throwing an error. We use these slash format dates in India a lot but they aren't listed in our region for some reason.
@Computergaga
2 жыл бұрын
@@crazydrifter13 nice work 👍
@crazydrifter13
2 жыл бұрын
@@Computergaga 👍🏼 that's what I should say. You are doing all the nice work. Thanks.
thanks for the examples... but I have 220109 type date in my dataset.. how to convert that ? 220109 is (9th January 2022)
@Computergaga
Жыл бұрын
You're welcome. You can use the Text to Columns technique and apply YMD. Or, you could use a formula such as =DATE(LEFT(A1,2),MID(A2,3,2),RIGHT(A1,2))
Hi, I have a list of different date/times in different types (I attached a small section of the list). They're all down as text but I really need it to be as a date format to do some calculations. I tried all the methods to change it but really unsure what else I can do, any help is appreciated! Mar 21 2024 12:01AM Mar 21 2024 12:01AM Mar 22 2024 12:00AM Mar 26 2024 9:17AM Mar 26 2024 12:00AM Mar 25 2024 12:00AM Mar 24 2024 12:00AM Mar 23 2024 12:00AM 25/03/2024 00:00:22 24/03/2024 00:00:22 23/03/2024 00:00:33 22/03/2024 00:00:21 21/03/2024 00:02:54
Its really helpful video for me..🎈✨
@Computergaga
3 жыл бұрын
Excellent!! Great to hear Sujith.
Thanks! Now if I delete the first column, the referenced data in the second will vanish! How to avoid that?
@Computergaga
2 жыл бұрын
You're welcome. Convert the second column to values only. You can do this by copying the rage and past as Values and Number Formatting over them.
Thank you. How do I change '21-Oct-2020' to 21-OCT-2020'? I'd appreciate your help.
@chhayeav
3 жыл бұрын
=UPPER(TEXT(E3,"dd/mmm/yyyy"))
@Computergaga
Жыл бұрын
You can use the UPPER function to convert case
if my Excel file does not recognise the values as dates, is there any solutions?
@Computergaga
Жыл бұрын
Depends on their structure, but sounds like we need to use an examples such as this formula =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) This formula is used on a date in the yyyymmdd format, so if your is different, a variation of the formula is required.
Sir in previous video I have asked a question in your comment box about decimal value plz solve the problem
@Computergaga
5 жыл бұрын
Hi Afzal, sorry I get a lot of comments and other messages. I'm not sure where that comment is or what video it is on.
@afzalzamiransari
5 жыл бұрын
@@Computergaga sir dont say sorry you are very helpful for me
@Computergaga
5 жыл бұрын
Thank you Afzal.
@afzalzamiransari
5 жыл бұрын
@@Computergaga sir did you find my comment on another video
@Computergaga
5 жыл бұрын
No I didn't
How do I EXACTLY order data by date if its written in format looking like this "Apr-13-21"?
@Computergaga
2 жыл бұрын
The first task is to convert them to dates, because they are probably stored as text. Then you can sort them as usual. This video shows how to convert them to dates quick and easy - kzread.info/dash/bejne/eHmm08qTnJS8lNY.html Text to Columns is a quick approach.
thanks - was about to throw out my laptop when just in time i saw your video
@Computergaga
4 жыл бұрын
Excellent! Happy to help you and save a laptop.
@abhiseksingh4886
3 жыл бұрын
Would do anything to save ur laptop. Along with this videos U can save it by watching a video at "off to office" dedicated to text to column feature.
Dude i have big issue.. How can i transfer the date from 21.11.2022 to 11/21/2022
@Computergaga
Жыл бұрын
The Text to Columns Approach should fix this. Or the DATE function can be used along with LEFT, MID and RIGHT to extract each date element. Depending on your region, you may need formatting to present it in the M/D/Y structure.
4-Jul returns #value..can y help me
@Computergaga
Жыл бұрын
#VALUE! is an error normally indicating the value is off the wrong type i.e., the data is stored as text and not a number. I can't provide a specific answer outside of the video examples here as it depends on many factors such as region. But we may result to splitting the value before converting it.
so what do you do when it comes up that it cant find the ''.'' to replace ;(
@Computergaga
4 жыл бұрын
Sounds like they are already date values and just need formatting. Or you accidentally typed a space before or after the .
@leewilliams9137
4 жыл бұрын
@@Computergaga cheers formatted 😉
@abhiseksingh4886
3 жыл бұрын
If u use a tilde sign before wild cards excel will search even asterisk also. Text to columns at length has been explained in one of the videos at "off to office". Apart from the date aspects u will find other interesting usages of text to columns.
i have 10-Sep-12 and isnt date how to convert it folk?
@Computergaga
4 жыл бұрын
If Text to Columns didn't work, try the DATEVALUE function.
@abhiseksingh4886
3 жыл бұрын
It's already a date format.let us say if it has been entered into cell using =" 10-sep-20". U can use copy and paste special to retain value only from the copied cell. And using find replace get rid of = and ". I have now converted it into date which u can check. Or u can check even that with date value function. For further understanding u can onve visit "off to office" and c if it clears ur doubt.
dear all, anyone know about " Thu 5/5/2022 10:26 AM " how to convert to date.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Instead of using the Value formula just add a zero to the cell value(after periods are replaced with slashes). (Still need to format as date)
@Computergaga
5 жыл бұрын
Thanks RickK Evil for the tip.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
how to change Date to number like (Aug-20) to (08-20) in Excel
@Computergaga
2 жыл бұрын
You can do this with custom number formatting Vamsi. Open Format Cells, click Custom and specify the format in the Type field as MM-YY.
Use replace
@Computergaga
Жыл бұрын
I have for one specific instance
Impressive! How can I give you double "Like", or double "Subscribe"?😄
@Computergaga
8 ай бұрын
You just did 👍 thank you
27/04/2019 is not a correct format. How can I put it in the correct format 04/27/2019?
@Computergaga
3 жыл бұрын
You can use Text to Columns. On the third step, specify that you want the MDY format.
@abhiseksingh4886
3 жыл бұрын
Text to columns at length has been explained in one of the videos at "off to office". Apart from the date aspects u will find other interesting usages of text to columns.
@mikibg8134
3 жыл бұрын
YOU CAN USE =TEXT(A1;"mm/dd/yyyy")
Unfortunately No example worked for me. How to convert text into date for text like "Jan 01, 2015"
@Computergaga
3 жыл бұрын
I just tried it using Text to Columns and specifying MDY format. It worked perfectly.
@naveenofficial3680
3 жыл бұрын
@@Computergaga It's not working brother. I downloaded stock data from investing.com and date is in text format. Not able to convert using text to column feature
@Computergaga
3 жыл бұрын
Ok. I'm not sure why. There are other formulas and Power Query techniques to do it. But the sample data I used Text to Columns was enough. And the other techniques are more extensive.
@naveenofficial3680
3 жыл бұрын
@@Computergaga Ok big brother 👍 thanks for reply. Your each and every tutorial is very interesting and useful. They helped me a lot in creating complex excel models. Now I'm able to do my research systematically. Thanks again.👏👏👏
@Computergaga
3 жыл бұрын
You're welcome, Naveen. Thank you for your comments.
Listening your accent reminded me of Peaky blinders😁 However, it was a great lesson. I got to know a lot new things. Thanks
@Computergaga
2 жыл бұрын
That's great. Thanks 😊