Convert date format?

[excelbetter]

How do you convert this date format: [FONT=&quot]2017-03-18

To this: 03-18-2017

Any help is appreciated.[/FONT]<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>

 

[cooper645]

have you tried selecting the cells,

Right click > Format Cells > Custom > "select the TYPE box" > enter dd-mm-yyyy

This should achieve what your after in the cleanest way I think.

 

[excelbetter]

have you tried selecting the cells,

Right click > Format Cells > Custom > "select the TYPE box" > enter dd-mm-yyyy

This should achieve what your after in the cleanest way I think.

Thank you. That worked.

One more if you have time....

How do I convert the following to just a date? <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>2017-03-18T00:00:00.000Z

 

[jtakw]

have you tried selecting the cells,

Right click > Format Cells > Custom > "select the TYPE box" > enter dd-mm-yyyy

This should achieve what your after in the cleanest way I think.

Hi,

Think you mean mm-dd-yyyy

 

[sureshxcell]

Try this

Assuming your original date is in "E6"

=TEXT(MID(E6,1,10),"mm-dd-yyyy")

 

[jtakw]

Thank you. That worked.

One more if you have time....

How do I convert the following to just a date? <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>2017-03-18T00:00:00.000Z

Hi,

How about this:

Book1
	A	B	C
1	2017-03-18T00:00:00.000Z		03-18-2017
Sheet18

Cell Formulas
Range	Formula
C1	=TEXT(LEFT(A1,10),"mm-dd-yyyy")

 

[excelbetter]

THank you. When I do this and then create a pivot table, it tells me it can't group by date. I think I must have the date format wrong, or maybe it's text?

 

[excelbetter]

Hi,

How about this:

	A	B	C
1	2017-03-18T00:00:00.000Z		03-18-2017

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet18​

Worksheet Formulas Cell Formula C1 =TEXT(LEFT(A1,10),"mm-dd-yyyy") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WHen I do this the resulting cell shows #NAME ?

 

[excelbetter]

Try this

Assuming your original date is in "E6"

=TEXT(MID(E6,1,10),"mm-dd-yyyy")

This worked however when I go to create a pivot table, I can't group by the date field.

 

[excelbetter]

Here's a link to the excel file I'm having issues with: https://www.dropbox.com/s/hden1uxk7p9wdzd/DATE ISSUE FOR PIVOT TABLE.xlsx?dl=0