Having trouble formatting dates in a cell

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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???
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top