1

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.

Information Technology
  • 2,243
  • 3
  • 30
  • 46
  • 1
    maybe interesting? [Convert Date from yyyymmdd to mm/dd/yyyy Excel](http://stackoverflow.com/questions/13056051/convert-date-from-yyyymmdd-to-mm-dd-yyyy-excel). It may be worth looking at the _Related_ links on the right-hand-side of that page? – Ryan Vincent Aug 28 '16 at 14:24
  • I already did. That example uses a fixed position assumption for identifying yyyy, mm, and dd. It does not look for a token separator like "." which is used as a separator. – Information Technology Aug 28 '16 at 14:32
  • 2
    Ok, then it would help us if you mentioned that in your question? i.e. Your question repeats about fixed format input - a lot. No mention _anywhere_ about using '.' as a token separator. Please update your question about using '.' as a token separator for variable length days and month strings. It will make it clear what you really want. – Ryan Vincent Aug 28 '16 at 14:41

1 Answers1

5

Please try: 

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

or: 

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

and Format m/dd/yyyy

pnuts
  • 58,317
  • 11
  • 87
  • 139