Convert text to date format

[IKHAN]

Hi , Need help in converting excel dates to proper format.

If date such as Sun 6/19/2016 12:09 AM is copied onto cell needs to convert to format ddd mmm dd, yyyy - hh:mm AM/PM (Sun Jun 19, 2016 - 12:09 AM

Tried changing cell format to above, used datevalue function - Gives an error #value

Using excel 2010


Thank you

 

[NARAYANK991]

Hi ,

Can you mention what your system date format is ?

Is it mm/dd/yyyy or dd/mm/yyyy or variations of these two ?

Narayan

 

[NARAYANK991]

Hi ,

If your system date format is mm/dd/yyyy , then a formula such as :

=RIGHT(A1, LEN(A1) - 4) + 0

where A1 has the data , will convert the data to a valid date.

Once the data is converted to a valid date , you can apply any Excel date format , and the date will be appropriately displayed.

Narayan

 

[xlstime]

Seems your system date format is not correct.

please follow chandoo date conversion method

http://chandoo.org/wp/2010/03/23/text-to-date-convertion/
https://exceljet.net/formula/convert-date-to-text

 

[IKHAN]

System date mm/dd/yyyy

 

[Marc L]

Hi !

If a date in a cell is a true / real valid date (as number and not text),
there is nothing to convert but just needs to amend the cell format !
​

 

[IKHAN]

Agreed @Marc L ...Its a text in format (Sun 6/19/2016 12:09 AM) need conversion to format ddd mmm dd, yyyy - hh:mm AM/PM (Sun Jun 19, 2016 - 12:09 AM

 

[Marc L]

So all the necessary is already in post #4 first link !
​

 

[fracimarcos]

System date mm/dd/yyyy

 

[asKeygeek]

I found below formula helpful:

=IF(B1=””,””,IF(DAY(B1)>12,DAY(B1)&”/”&MONTH(B1)&”/”&YEAR(B1),VALUE(DATE(YEAR(B1),DAY(B1),MONTH(B1)))))

Copied from: http://www.askeygeek.com/excel-formula-to-convert-ddmmyyyy-to-mmddyyyy/