Having trouble formatting dates in a cell

[Marq]

I have a range of dates that are written as, for example, 20190102

I am trying to format it to show as 1/2/19 and im not having any luck.

I've tried the format cells/date and its not changing.

Ive copied and pasted the 20190102 into another work book as a value and tried to format it and had no luck.

Ive done text to columns on the column that has the dates and no luck.

So what am i doing wrong???

 

[Joe4]

Do a Text to Columns on it, but when you get to Step 3, be sure to choose the Date option, and select the appropriate date option.
It would be YMD or YDM, depending on which comes first in your date structure, Month or Day (I cannot tell because your example date could either represent January 2 or Feberuary 1).

 

[Marq]

doing it...im choosing DMY to show 1/2/19. Nothing is changing

my steps are:

Highlight column
Text to Columns
default which is delimited (then next)
default which is tab checked (then next)
Chose "Date"...set to DMY.."Destination is defaulting to the fist cell of highlighted column which is $D$1

I click finish and nothing changes

D1 is the title of the column..my dates begin at D3 and go down to D1500

 

[Joe4]

doing it...im choosing DMY to show 1/2/19.

No, that is not correct.
You don't enter the format you want there, you choose the current structure that the data you are trying to convert (you control the format of the output through normal cell formatting).
So, in your data example of 20190102, we know that the year comes first (2019).
Does the "01" part represent the month or the day?

If Month, then you will select "YMD".
This tells Access that in the data coming in, the year is listed first, then the month, then the day.
This is necessary, as you can see how your value could represent two different valid dates. So Excel needs to know the structure of the data to know to convert it properly.

If "01" represents the Day, then you will select "YDM".

Note that all dates in Excel are really stored as numbers, specifically the number of days since 1/0/1900 (change any valid date to General format, and you will see this).
So all a date in Excel really is, is a whole number with a special date format applied to it.

 

[Marq]

No, that is not correct.
You don't enter the format you want there, you choose the current structure that the data you are trying to convert (you control the format of the output through normal cell formatting).
So, in your data example of 20190102, we know that the year comes first (2019).
Does the "01" part represent the month or the day?

If Month, then you will select "YMD".
This tells Access that in the data coming in, the year is listed first, then the month, then the day.
This is necessary, as you can see how your value could represent two different valid dates. So Excel needs to know the structure of the data to know to convert it properly.

If "01" represents the Day, then you will select "YDM".

Note that all dates in Excel are really stored as numbers, specifically the number of days since 1/0/1900 (change any valid date to General format, and you will see this).
So all a date in Excel really is, is a whole number with a special date format applied to it.

aaahhh..ok..that fixed it..I had to chose YMD....That got it working right.

Thank you!

I understood it as I could choose which format i wanted...not that I had to chose the existing format.

 

[Joe4]

You are welcome.

I understood it as I could choose which format i wanted...not that I had to chose the existing format.

Yes, it is understandable. When you think about it in detail, it makes sense, that you would need to tell Excel what the structure of the data it is reading is in, so it knows how to convert it.
I added that last part, because once you understand that the date it is converting to is really just a number, it makes sense that you do not need to tell it the format you want the data in after the fact. It doesn't do that - you do that yourself afterwards with cell formatting.