How To Quickly Convert Text To Dates With Find And Replace In Excel
ฝัง
- เผยแพร่เมื่อ 4 ก.ค. 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video I explain a shortcut with Find and Replace to quickly convert text to dates in Excel. The example is an Excel file that has been exported from Quickbooks Online. However, this technique will work on any file that has dates stored as text.
Download the sample Excel file: www.excelcampus.com/tips/text...
I also explain how to determine that the value in the cell is text instead of a date by using either the number format drop-down menu, or the filter drop-down menu. In the filter drop-down menu the dates will NOT be grouped, and instead listed out individually.
Please leave a comment below with any additional tips or techniques you have for converting these dates to a date data type. Thank you! 🙂👍
Additional Resources:
How the Date Calendar System Works in Excel: • Understand How The Dat...
Convert Text to Dates with Flash Fill: • Convert Text to Dates ...
How to Add Days to Dates in Excel:
• How to Add Days to Dat...
#MsExcel #ExcelCampus
00:00 Introduction
00:12 Converting Text to Date
00:32 Number Format Drop-Down
00:49 Filter Drop-Down
01:39 Use Find & Replace to Evaluate Cells
![[ LIVE ] Rank เล่นแต่แครี่ ยาวๆถึง 100ดาว “วันนี้มีกิจกรรมลุ้นรับสกินมาบอกต่อ โก๋แก่ช็อกโกบอล x ROV”](http://i.ytimg.com/vi/5NNgkhWj57Y/mqdefault.jpg)
Thanks Jon.. another way to do this would be to just select entire column & pressing Alt AEF or Alt DEF..
Thank you, This SAVED my life
Thank you - this is exactly what I needed, short and simple.
Hi Jon. I tried to post this on the blog where many people from non-USA countries had the issue where this did not work. Somehow my post there doesn't go through so I thought I'd post it here:For the English v American date notation issue:This is a simple user-defined function I wrote that converts dates incorrectly recognized in American format back to English format but should work for the other way round as well. So far it has worked for me and for this example it works because it forces excel to look at the cell and then still converts it to an English format so will still keep the correct date ie. for the find replace method, excel will change '06/02/2019 to 6 Feb 2019 (for English users) but using this function it will force excel to look at the cell, and then convert 6 Feb to 2 June.Note: only use it when you know that the dates are in American notation - if your PC recognizes 1/2 as 1 Feb (and that is what you wanted!), this function will convert it to 2 Jan and muddle your dates!Btw - Great website Jon and greetings from Namibia! I have learnt so much from you! The function name is merely in jest and so that I can remember it directly from excel :-) You will need to copy the below function into your personal workbook VBA editor which you can either access from any other VBA code or directly from excel with the following function:
=PERSONAL.XLSB!IDONTSPEAKAMERICAN(A3)
Jon has more material on functions.As mentioned, I think American users can also use the same function if they have the reverse issue (eg. you import a document where 1/2 is 1 Feb but imports to your PC as 2 Jan) but I wouldn't recommend calling it PERSONAL.XLSB!IDONTSPEAKENGLISH(A3)Here's the function:
***
Function idontspeakamerican(inputDate As Date)'converts all American dates back to English dates eg. 5/4 becomes 4 May - _
the function will convert it back to 5 April but leave 13 Jan as 13 JanDim y, m, d As Integer
Dim isAmerican As Booleany = Year(inputDate)If day(inputDate) < 13 Then
isAmerican = True
Else
isAmerican = False
End IfIf isAmerican Then
d = Month(inputDate)
m = day(inputDate)
Else
d = day(inputDate)
m = Month(inputDate)
End Ifidontspeakamerican = DateSerial(y, m, d)
End Function
Thanks Jon. Brilliant idea. Simple and quick way to solve..
Thanks Manikandan! :-)
What a nice trick Jon!
I learned a lot of something I needed!
Thanks so much!
Jon your video was so helpful, The words are not enough to Thank You.
I had this issue with 360000 rows data and then saw your video and converted them to dates.
Thanks for the useful trick which saved my valuable time from using text to columns and date function.
PERFECT! 3800 Cells saved!
Very useful, I was looking for a solution like that for while until I found your video. Thanks for sharing !!!
This is exactly what I was looking for.. Thanks for this useful tip Jon :)
Thank you!
Excellent!
Great tip
It brings a cheer in my heart. Long time I am waiting for such a solution. Thanks sir
Thanks so much, this really helped and saved me sooo much time 😊
Helpful tip! Finally I've figured this out. Thanks!
Glad it helped! 😀
Very Helpful
Helpful!!
Thanks.
Good tip. Not using quick books but I come across this problem with out reports and it has given me some good ideas on how to manage this problem. Thanks
Thanks Penny! 🙂
Great tip! Thanks for sharing, very helpful on my daily job.
Thanks Shirley! Happy to hear this tip will help you!
you are the best👍👍👍
Thanks Jon. This is very useful.
Thanks Anill! 🙂
I like these videos. Thanks.
*Amazing trick Joan*
*Thanks for sharing*
*Ontime Edu* 👌👌🙌
Brilliant, thank you! I fixed 915 dates in a second.
Hi Jon. The fastest way to tell if it is text or numbers is to look at alignment. Text is general left aligned, while numbers are right.
Great suggestion! Thanks Dennis! :-)
Great tip, works wonders
Glad it helped 😀
Thanks this is a really helpful tip!
Amazing!
Love you bro
Helpful video
I struggled for hours before finding this. thank you so much!!!!
Glad I could help, @lahiriishani ! 😀
Awesome!!! Thanks!! Such a small action but can do so much :-)
Brilliant idea Jon,
I used to multiply each cell value with 1
So, that it can be converted into date format Or We can use TEXT to Column wizard of DATA tab.
Thanks brother..
thanks for such informative vedio
This is amazing!
Thank you! 😀
NICE THANKS
Awesome tip! I used to to it using Text to columns, or - - in formulas.. But this one is easier than text to columns for that matter! Thanks again for the nice tip!
Thanks Ali! :-)
That is good tip thank you so much
Thanks Rosa! 🙂
Thanks alot for ur Effort , understandable Explanation , and obvious Voice . Another Tip for Converting the Date Format from Text to proper Date Format : Select the whole Column , Data Menu , Text to Column , last step choose Date and then click the finish Button .
Thanks, Murad! 😀
THANK U SO MUCH VERY HELPFULL VIDEO FOR QUIK RESULT
You're welcome, CA! :)
easy done, thks.
Welcome @armindolopes2248😊
very helpfull
Thanks a lot!
I have 2 other methods.
The first one is with function CNUM ( I use Excel in French but I think it's the same name) : =CNUM(the cell with the text).
The second one is with the function DATE. In this function there are 3 Arguments (Year, month and Day).
You write : =DATE(YEAR(the cell);MONTH(the cell);DAY(the cell)).
YEAR, MONTH, and DAY are functions.
thanks From Bangladesh
bro should have got an Oscar for this. bravo
Haha, thanks for the feedback! 😀
just one word for you "WOW"
text to column (Alt DE)
I like to keep the imported data as it came, so I use a second sheet for all conversions, etc.
For this I'd use the DateValue() function.
Super
Hi Jon.. great tip. Another method is to put the cell pointer in a blank cell and CTRL+C to copy, then highlight the cells with the text dates (or any other text values) and press CTRL+ALT+V for Paste Special and choose "Add" (or "Subtract") under Operation command grouping. Click OK and all the text dates will have 0 added to them and be converted to true values. This works with any text values that just need the nudge to be recognized as true values by EXCEL. Like your Replace operation or a double unary operation.. Paste Special Add or Subtract 0 gives EXCEL what it needs to convert to true values. You can also do it by putting a 1 in a cell and "Multiply" or "Divide" instead of "Add". I like the Add 0, because you can use a blank cell.. one less step. I like your Replace method.. never thought of using it to find and replace the same character to get the value conversion.. great! Thumbs up!
Awesome! Thanks for sharing this paste special method Wayne. I don't think I've ever used that one, but it's another great trick to know for text conversion. 👍
@@ExcelCampus Thanks Jon.. just saw below from CA Vivek Gupta using ALT, A, E, F or ALT, D, E, F.. those are great too and easy to do right from the keyboard either in a single cell or over a range. These exercises always produce some useful and creative solutions.. great learning! Double Thumbs up!!
Hi Jon Thanks for this. I have data from a CSV file when i was in a Query i noted that the date data was in text. I have tried your method and a few other methods but nothing is converting the data to number or date format.
Hi Jon, nice trick, but we can automate the entire process by pasting Quick book exported excel report into a folder and create Excel Table using Power query, Powe query can easily convert these kinds of odd date formats into proper dates types, also do many other wonderful things, since it will be an automated process, next time user needs to paste the same file in folder and final report can be available for further data anlaysis with data > refresh!
Yes! Power Query is another great solution for this. Thanks! 🙂
Someone may have said this but the way I change text to dates is to highlight the entire column, then copy it, then paste it as a value and it's in number format. Everything out of SAP to excel is always formatted in text so I use this method a lot. It's very fast.
it didn't work for me .. is there something else i can do to convert the General to Date
Thanks. Please can you show how to change dates with time in it from text to normal data formate for example: 07/15/2022 09:55 .
Similar to Susanta Kumar below I use 'paste special' functionality to 'multiply'' everything by 1 without having to create a new column. This is the only time I use the 'Operation' functions in 'paste special'.
how to do that conversion using the CSV file?
Another possibility: if a text/date is stored in A3, this formula: =--A3 will also perform the conversion.
Very cool! Thanks Abbott! 🙂
@@ExcelCampus Thanks. I can't take credit for it, though I don't recall where I first saw it. Not sure why the unary operator works here, but it does.
Thanks Abbott, much appreciated 👍
@@muditdave7772 You're welcome. Again, it's not entirely clear to me why it works, but it does :) It also seems to work with times stored as text.
To autmate the process we can record a macro and rthen run it.
Thanks Ignacio! 🙂
I use Text To Column
I think we can also do it using text to column
Hi Jon, thanks for all excellent video
Could you please help me, if there is a way to change to dates to Fiscal year in a Pivot Table, our financial year starts June to July and Excel sorts in Jan to Dec.
Kind Regards, Don
Hi Jon I am trying to do the reverse, the exported file in csv changes my part numbers to dates? i.e.12-2003 becomes Dec-03 in a custom format?
I usually use the status bar to determine if they are really dates or not.
Great tip! Thanks Jonathan! :-)
hi jon, i actually have a "date" text in a month day year format like this "6/14/2024 12:00:00 AM", if i use find and replace technique like you shown, it only converted some part of the data where its suitable for day month year format like "6/12/2024 12:00:00 AM", how can i convert fully all my date with mm/dd/yyyy format to the date type? i've been struggling with this for quite a while now, i'll appreaciate for your help
Hi Jon can you please upload a video for my request ( I have workbook with multiple worksheet. I want to combine all the worksheet data into one worksheet using VBA can you help on this)
It didn't work for me. The text dates remained so after the replacement. Tried replacing / with / and / with -. Cells remained "General"
Same
I get picture links that I have to convert... so far no quick step has helped. I have to enter the cell go to the end and tab to the next and then it converts to a link.... any ideas?
Hi Jon. The text column template is mm/dd/yyyy. In Canada and the UK the default is dd/mm/yyyy. The method in this video doesn't seem to work in that scenario. What's the alternative?
Record a macro, for the first time you select column with the dates, then you go to View/ Macro / Record Macro, set it to Perosnal workbook and select for example CTRL+SHIFT+D so it' not triggering anything else, now record steps above and changing date format, 2 steps esentially, now stop the Macro and now when ever you need to quick convert select columns and press CTRL SHIFT D...
How do you extract the date from 011600Z MAR 20 (which is a ddhhmm[ Z= zulu time] mmm yy format, to a "dd mmm yy" format? That's a tough one. It's the text part (MAR for March) that I can't figure out. I use "right", "mid" and "left" but excel doesn't recognize the MAR (text) as the month of March.
I'm trying to find the solution to convert dates in the format 23 January 2019 to 23/01/2019. Using text to columns gives some crazy answers (e.g. all the dates end up being 1900, or it sees the year 2010 as October 20). So I'll be interested in that please!
i idownloaded data from tableau and the dates are in "Jan'21" format. Once I create a pivot, excel sorts the dates alphabetically. I need them in calendar format and I am not able to convert them into dates. (month-year) format. Can you help here?
Hi i would like to know if this format of date 110323 can be converted to excel dates? Thank in advance for the help
Hi CB! Yes, it can!! 😀 One way is to use Excel functions to pull the different date pieces apart and then put them back together. =DATE(RIGHT(A1,2)+100, LEFT(A1,2), MID(A1,3,2)) would take your date listed in Cell A1 and produce 11/3/2023. The +100 is for the year 2000 and later. Hope that helps!
might i know how to convert amercian date in text form to uk date form in date ?
Hi Jon, how to convert text into value in a date in excel example Sun, 18 Jul, 2021, 10:38 pm IST, I'm new to excel?
Tried the find, replace method, but it did not work. Is it possible since I'm using older (XP version) Excel that it won't work?
Question: what happen when the date has a time ?
Dear sir , i appreciate you for sharing your precious knowledge with us but i am facing some problem in this
some of my text dates are change with your method but some are as remains as it is
please help me with this sir how can i change all my text dates in number format
Text to date:
th-cam.com/video/7uDnF_rHfSM/w-d-xo.html
Hi Jon,
What can I do to format dates listed in a table this way- May 21 2019, July 05 2019, January 01 2019? With the month names and no comma after the day, I'm having trouble formatting these as a date. What can I do?
I have the exact same issue T.T Any solutions?
Nice
But some one problem
Date 2/23/2014 this convert in date to short out this problem sir
Hi Jon. How to reformat this text and date to date?
For example: ISSUE DATE-1-09-2017 -> Date is 01-Sep-2017 ????
Thanks
Will this trick work in excel 2007 as well??
Yes
How to convert 16-01-20 17:38 into excel supported date and time format?
How to convert text date such as 2nd April to 02-04-2022
Hi Jon, I've imported data from the web in the form of "Dec 29, 2017". How do I convert that kind of text to dates?
Try Power Query, import using Get and Trasnform in to excel.
The DATEVALUE function works well if that's available to you.
Great question Shuska! In addition to the other awesome replies, you might want to try this Find and Replace trick using the comma character. I haven't tested, but it might work?
Thanks! 🙂👍
How to change it when the date is recognized in mm/dd/yyyy format??
Just change the date format to mm/dd/yy through 'Format Cells...'
Use text to colums wizard. In one of the wizard steps (3rd from memory) you can identify that they are dates and in what format.
Unfortunately, the link to the sample file is not working :-)
Thank you for letting me know! I just fixed it. 👍
My dates are spread across rows in the header, this technique won't work. can you help me on this
Hi Kinley! 😊 You can also use this same technique by selecting a row instead of a column. The find and replace steps still apply. I hope that helps you! 🙂
how to convert timestamps to date
Let say 1/1/2022 and 2/1/2022 is Sat & Sun by auto is week 1.. how to formulate 3/1/2022 as a week 1? and how to format as WEEK 01, WEEK 02 ...etc
Hi Jon. For some reason, it didn't work for me.
Sorry to hear that Lam. It could be because your regional date format is not the same as the date format of the data in the sample file. In this case the format is mm/dd/yyyy. I added a section to the article that explains more about this, and will follow-up with alternative solutions. www.excelcampus.com/tips/text-to-dates-find-replace
I hope that helps.
Will check and let me mu fidinds
dont work
=DATE(RIGHT(A3,4),LEFT(A3,2),MID(A3,4,2)) work
Hmm. I did exactly what you very well explained and it didn't work for me. I have tried: Istext(cell) False or Number, multiplying by 1 or adding a zero and some other hacks..No success