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
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.
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 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 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.
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.
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!!
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!
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!
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.
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.
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 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!
I am having a very specific issue with cleaning up date and I can't seem to find a video. I've tried the TRIM function which doesn't seem to work. How can I get in touch with you for this specific need? Thank you!
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...
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
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)
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!
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'.
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?
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
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?
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 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 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 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! 🙂
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.
This is even faster than your other video about converting text to dates. I’m glad I decided to check out this video. Thank you!!
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.. another way to do this would be to just select entire column & pressing Alt AEF or Alt DEF..
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.
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! 🙂
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! 😀
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! :-)
Thanks Jon. Brilliant idea. Simple and quick way to solve..
Thanks Manikandan! :-)
Thank you - this is exactly what I needed, short and simple.
Jon your video was so helpful, The words are not enough to Thank You.
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.
Very useful, I was looking for a solution like that for while until I found your video. Thanks for sharing !!!
What a nice trick Jon!
I learned a lot of something I needed!
Thanks so much!
Helpful tip! Finally I've figured this out. Thanks!
Glad it helped! 😀
This is exactly what I was looking for.. Thanks for this useful tip Jon :)
PERFECT! 3800 Cells saved!
Thanks so much, this really helped and saved me sooo much time 😊
I struggled for hours before finding this. thank you so much!!!!
Glad I could help, @lahiriishani ! 😀
Great tip! Thanks for sharing, very helpful on my daily job.
Thanks Shirley! Happy to hear this tip will help you!
Brilliant, thank you! I fixed 915 dates in a second.
THANK U SO MUCH VERY HELPFULL VIDEO FOR QUIK RESULT
You're welcome, CA! :)
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.
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!!
Very useful ❤
Great tip
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! :-)
Very Helpful
It brings a cheer in my heart. Long time I am waiting for such a solution. Thanks sir
thanks for such informative vedio
Thank you, This SAVED my life
you are the best👍👍👍
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! 🙂
Thanks Jon. This is very useful.
Thanks Anill! 🙂
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.
*Amazing trick Joan*
*Thanks for sharing*
*Ontime Edu* 👌👌🙌
bro should have got an Oscar for this. bravo
Haha, thanks for the feedback! 😀
Awesome!!! Thanks!! Such a small action but can do so much :-)
Thanks you saved me
Excellent!
Thanks brother..
Helpful!!
Thanks.
That is good tip thank you so much
Thanks Rosa! 🙂
I like these videos. Thanks.
Love you bro
It worked!
Great tip, works wonders
Glad it helped 😀
NICE THANKS
Thank you!
This is amazing!
Thank you! 😀
Super
easy done, thks.
Welcome @armindolopes2248😊
Helpful video
very helpfull
Thanks this is a really helpful tip!
thanks From Bangladesh
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.
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 .
I think we can also do it using text to column
Amazing!
just one word for you "WOW"
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
I usually use the status bar to determine if they are really dates or not.
Great tip! Thanks Jonathan! :-)
It didn't work for me. The text dates remained so after the replacement. Tried replacing / with / and / with -. Cells remained "General"
Same
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!
I am having a very specific issue with cleaning up date and I can't seem to find a video. I've tried the TRIM function which doesn't seem to work. How can I get in touch with you for this specific need? Thank you!
text to column (Alt DE)
To autmate the process we can record a macro and rthen run it.
Thanks Ignacio! 🙂
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.
Thanks a ton for sharing it
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...
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?
it didn't work for me .. is there something else i can do to convert the General to Date
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:
ruclips.net/video/7uDnF_rHfSM/видео.html
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)
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!
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'.
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?
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, 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?
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?
I use Text To Column
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?
Nice
But some one problem
Date 2/23/2014 this convert in date to short out this problem sir
Question: what happen when the date has a time ?
Hi Jon. How to reformat this text and date to date?
For example: ISSUE DATE-1-09-2017 -> Date is 01-Sep-2017 ????
Thanks
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! 🙂👍
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.
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 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?
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 do that conversion using the CSV file?
Will this trick work in excel 2007 as well??
Yes
might i know how to convert amercian date in text form to uk date form in date ?
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.
How to convert text date such as 2nd April to 02-04-2022
How to convert 16-01-20 17:38 into excel supported date and time format?
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
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.
how to convert timestamps to date
Will check and let me mu fidinds