Converting a date...

**hyde0429** · 02-20-2013, 11:52 AM

Okay, I've been reading trying to figure out how to do this. I'm sure it's not that complicated.

I have some date with a date that is formatted incorrectly. I can format the cells easily, but I can't seem to figure out how to change the data to match the way I have it formatted.

See the picture below / attached:

date_issue.jpg

So my question is, is there a way to make the date stored in the column to match what it is formatted to look like?

**Jakobshavn** · 02-20-2013, 01:01 PM

You show only the date/time values, not the desired format.

What is the desired format??

**Pepe Le Mokko** · 02-20-2013, 01:03 PM

Perhaps post a small sample instead of a picture which does not help

**hyde0429** · 02-20-2013, 01:15 PM

Okay, sorry. Let me try to explain this a little better. I have a date in a column. The date is entered as "7/29/1991 12:00:00 AM". I can right click on the cell, and change the formatting so it reads as "1991-07-29 00:00:00". However, when the field is highlighted, you can see the old formatting in the formula area. So what I want to do is change the formatting completely, so that the format "1991-07-29 00:00:00" is literally what the data in that cell looks like.

In short, I want co convert the dates from "7/29/1991 12:00:00 AM" to "1991-07-29 00:00:00".

**hyde0429** · 02-20-2013, 01:46 PM

Hopefully this makes more sense...

**Jakobshavn** · 02-20-2013, 02:00 PM

Re-formatting the cell changes the display within the cell. The value displayed in the Formula Bar only partially mimics the cell display.

For example, enter:

33448

in an un-used cell.

Then format the cell as Custom > yyyy-mm-dd-hh:mm:ss

The cell will display:

1991-07-29-00:00:00

but the Formula Bar displays:

7/29/1991 12:00:00 AM

**Pepe Le Mokko** · 02-20-2013, 03:41 PM

Originally Posted by hyde0429

Okay, sorry. Let me try to explain this a little better. I have a date in a column. The date is entered as "7/29/1991 12:00:00 AM". I can right click on the cell, and change the formatting so it reads as "1991-07-29 00:00:00". However, when the field is highlighted, you can see the old formatting in the formula area. So what I want to do is change the formatting completely, so that the format "1991-07-29 00:00:00" is literally what the data in that cell looks like.

In short, I want co convert the dates from "7/29/1991 12:00:00 AM" to "1991-07-29 00:00:00".

This is why a sample sheet would be useful. At the hand of your explanation, it is impossible to know if you are dealing with Custom formats or text looking like dates. So please post a small sample sheet of your data