In Vba there is a method through which we can convert a given string to a date, and the method is known as CDATE function in vba, this is an inbuilt function in VBA and the parts required for this function is to first convert the string to a number and then we convert the given number to a date.

I have tried to follow many options that have been provided but unable to get the text into date format.

Hello, I want to convert 1-Apr-20 to 20200401, how can i do it automatically?
In the next article, we will tackle the opposite task and explore different ways of converting Excel dates to text strings. Since Excel is not the only application you work with, sometimes you'll find yourself working with dates imported in an Excel worksheet from a .csv file or another external source. I think ideally I would merge that to show January 20th, 1930 in one column and then change the format after. Is there a way to "tell" DATEVALUE what the date string format is?=DATEVALUE(MID(B11,SEARCH("/",B11,1)+1,SEARCH("/",B11,SEARCH("/",B11,1)+1)-SEARCH("/",B11,1)-1) & "/"&LEFT(B11,SEARCH("/",B11,1)-1) & "/"&RIGHT(B11,4))I hope this will help, otherwise please do not hesitate to contact me anytime.=VLOOKUP(--RIGHT(A20,2), {1,"JAN";2,"FEB";3,"MAR";4,"APR";5,"MAY";6,"JUN";7,"JUL";8,"AUG";9,"SEP";10,"OCT";11,"NOV";12,"DEC"}, 2,0)&"-"&MID(A20,3,2)Hi! Thank you.

I thank you for reading and hope to see you next week. In case of a 2-digit year, Excel will ask if you want to convert it to 19XX or 20XX.If you have multiple entries of this type, you can fix them all in one fell swoop - select all the cells with errors, then click on the exclamation mark and select the appropriate option.Usually, Error Checking is enabled in Excel by default.

I tried using converting text to date. I kept getting yyyy-00-dd. Excel will not recognize this as a date value and will go ahead and store it as text.

I tried using the convert text to date but I am not getting anywhere.I want advice on a formula please, I want to to generate a future date 6 months in the future to be calculated from a start date and a word, so for example: the first date would be in one column, the word high in the next column would generate a future date 6 months on, but only generated when the word high is in the second columnIf you want to remove the time value from the date, then use the formula that will refer to this cellCheck what date format you have installed on Windows. Select the cells which contains the dates you want to convert to other date formats. "How to convert text to date and number to date in Excel" Hopefully, you have been able to find a technique to your liking.

By replacing the full stops with slashes (/), Excel will automatically identify the values as dates. I thank you for reading and hope to see you next week.=DATE(IF (LEN(A1)=7,"20" & MID(A1,LEN(A1)-5,2), MID(A1,LEN(A1)-5,2)), MID(A1, LEN(A1)-3,2), RIGHT(A1,2))Please go to Format Cells, choose Number -> Custom Format and setI have a Problem.In Excel CellS5=IF(G5="","",">="&G5)this formula,when i enter a date in G5 as 01/04/20 the cell S5 shows the result as >=43922.What to do? The original number-date (161003) contains only 2 chars representing a year (16). Hello, mat, you can use this formula =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) I am using a date 12 September 2018 I then convert it to 091218. Instead, Excel may interpret this information as a text or string value only. I want to check to confirm the entered date is not older than the auto populated date. Took hours to figure out.I have used the DATEVALUE function in the past and it works fie for me when the date string is in d/m/yyyy format. It's because I'm in a different country and had to use the local format.

Is there a way to change the default syntax of typing dates without having to change the date format every new excel worksheet?Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. If you want something more sophisticated than predefined Excel date formats, please see If some stubborn number refuses to change to a date, check out It's a very common situation when a date is input as an 8-digit number like 10032016, and you need to convert it into a date value that Excel can recognize (10/03/2016).

There is another way, it returns exactly the numeric format that represents the date abbreviated by (/), it would be: '=Substitute(A1;"/";"") How do I do an IF formula to test which date is older i.e. 1437620400 how can I convert this number in to a date in excel?