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
2017-03-18T00:00:00.000Z	03-18-2017

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet18​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=TEXT(LEFT(A1,10),"mm-dd-yyyy")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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