CODEWITHSUNDEEP
exceldateexcel-formula
Information Technology
Information Technology

Reputation: 2333

How do I write a formula to convert a string data ("yyyy.mm.dd) to a common date format (mm/dd/yyyy)?

I have a data value in column "A" that contains a date formatted as "yyyy.mm.dd", such as...

2015.01.30
2015.02.06
2015.12.31

I'd like to write a function, in column "B", that converts the dates in column "A" to the following formats (mm/dd/yyyy), such as...

1/30/2015
2/06/2015
12/31/2015

My question: How do I write a formula in column "B" that looks for a string in column "A" (formatted as yyyy.mm.dd) and converts it to a new format ("mm/dd/yyyy")?

Thanks for any help you can offer.

Upvotes: 1

Views: 319

Answers (1)

pnuts
pnuts

Reputation: 59460

Please try: 

=TEXT(SUBSTITUTE(A1,".","/"),"m/dd/yyyy")

or: 

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

and Format m/dd/yyyy

Upvotes: 5

Related Questions