Convert text into date

[Cellardoor]

Dear Friends

I'm trying to convert text into date but i can't to it by any means

In the attached file i've placed the formulas i've used...

I've also tried, by vba, to replace the month (in this case, april, for 04) but...

- For 04-Apr-2014 00:52, after the macro, i had the correct form - 04/04/14 00:52

- For 02-Apr-2014 19:53, after the Macro, i had: 04/02/14 19:53

????

If it helps, i'm downloading the data from my Betfair account ( :D )

Can anyone help me?

Many thanks

Best Regards

Cellardoor

 

[NARAYANK991]

Hi ,

When dealing with dates , always follow a procedure as shown below :

1. What is your default ( operating system ) date format ? Is it mm/dd/yyyy or dd/mm/yyyy or any other ?

2. Since the DATEVALUE function expects a string value as its parameter , first verify that your input is a text string ; thus a displayed cell value of 02-Apr-14 19:53 can be either a text string that has been entered the way it is displayed , or it can be a date value ( which in Excel is a number ) formatted to display it in that particular format.

To find out whether it is a text value or a date value , you can use the TYPE function , which will return 1 if the value is a date , and 2 if it is a text string.

Once you have determined that your cell actually contains a text string , when using the DATEVALUE function , you need to ensure that the parameter being passed to it will be in the format that it expects. Thus , if your system date format is mm/dd/yyyy , and you use the DATEVALUE function with a text string as follows :

=DATEVALUE("23/03/2014")

it will return an error value , since the text string is also supposed to be in mm/dd/yyyy format.

In your case , if we take the entered text value as : 02-Apr-14 19:53 , you have used a DATEVALUE function as follows :

=DATEVALUE(MID(F3;7;3) & "/" & MID(F3;5;2) & "/" & MID(F3;1;4))

The input to the DATEVALUE function will thus be :

-14/pr/02-A

if I understand your version of the MID function correctly. You have to say whether this is a valid date string in your Excel version.

Narayan

 

[Hui]

Why not do Text to Columns ?
Works a treat