CODEWITHSUNDEEP
excelexcel-formuladate-conversion
Brian White
Brian White

Reputation: 39

Date Conversions In Excel 2010

I am looking for a solution to convert date data from a trade log into a different date format in an excel spreadsheet.

I currently have a date that shows 2007.01.03 18:01 (this cell is showing general and without date format). I need this to be converted/changed to reflect m/d/yyyy h:mm

Does anyone have any solutions for this conversion?

Upvotes: 0

Views: 47

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

To convert it to a "real" Excel date, you can use:

=--SUBSTITUTE(A1,".","/")

You can then either custom format the cell containing the date, or you can embed it in a text function to format it:

=TEXT(SUBSTITUTE(A1,".","/"),"mm/dd/yyyy hh:mm")

(I would have thought this might not work with windows regional settings of YDM, but it seems to work even then in some testing here.)

Upvotes: 1

camilo correa
camilo correa

Reputation: 1

Good morning,

i) Use ctr + L to reemplzar. by / ii) +1 ctrl - change the cell formatting in the custom tab in "Type" use the following format mm / dd / yyyy hh: mm.

Greetings is all. CCB

By Google Traductor

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152465

Here is another option:

=DATEVALUE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","/"))+TIMEVALUE(MID(A1,FIND(" ",A1)+1,999))

Then format the cell, as @tigeravatar stated, m/d/yyyy h:mm.

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

Assuming the 01 from your example is the month, and the cell containing the date string is in A1, this formula should work for you:

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),RIGHT(A1,2),0)

Then format the cell as m/d/yyy h:mm using Custom Format and copy the formula down.

If preferred, you can then select the column containing the conversion formula and right-click -> paste special -> values to convert it to values instead of formulas.

Upvotes: 2

Related Questions