How To Quickly Convert Text To Dates With Find And Replace In Excel

Показать описание

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.

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 to Add Days to Dates in Excel:

#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

Рекомендации по теме

Комментарии

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:

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 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

ethofmeyr

Thanks Jon.. another way to do this would be to just select entire column & pressing Alt AEF or Alt DEF..

VivekGupta

Thanks Jon. Brilliant idea. Simple and quick way to solve..

ManikandanRaju

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 .

muradayyad

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

pennywhittaker

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.

sekharispassion

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.

dennisd

Helpful tip! Finally I've figured this out. Thanks!

elainehaakstad

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.

fabricetanoe

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!

wayneedmondson