How to Format Cells for Dates?

[blueboxdoctor]

Seems like an easy enough thing to do (right click, format cells, date). However, it's not working out quite so easily.

I have an entire column for dates on this form I'm doing for work. I may have made a mistake, so not sure if there's a quick fix. I put in all the dates prior to selecting the format cell option for dates. I did it in a way that they show up like this:

21415
20515

and so on.

I figured if I went to format cell and dates it would recognize that I want it to split into three sections as such:

2/05/15

But it doesn't realize that's what I want it to do.

Now, my question is, is there a way to get Excel to just do this in one of the formatting options or am I basically in for a lot of inputting new date data?

Thanks for any help.

If I explained it poorly feel free to ask what I meant, I'm currently rather annoyed at it so I may have worded my post poorly.

 

[Comfy]

You'll have to do something like this:

Excel 2010

	A	B
1	21415	14/02/2015
2	20515	05/02/2015

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

Sheet1​

Worksheet Formulas Cell Formula B1 =DATE("20"&RIGHT(A1,2),LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You'll notice my dates are different to you as I am EU (dd/mm/yy) rather than US (mm/dd/yy)

 

[Jonmo1]

Try Data - Text To Columns
Deliminated - Next
UNcheck all options - Next
Select Date - MDY
Finish.

 

[Ron Coderre]

Try this


• Select your single-column list of "dates"
• Data.Text-to-columns...Click: Next...Click: Next
...Check: Date...MDY
...Click: Finish

Now, format those dates however you like.

Is that something you can work with?

 

[blueboxdoctor]

Try this


• Select your single-column list of "dates"
• Data.Text-to-columns...Click: Next...Click: Next
...Check: Date...MDY
...Click: Finish

Now, format those dates however you like.

Is that something you can work with?

Thanks, this helped for that.

Now, another question just came up.

I'm formatting the blank cells to be set for date, I'm going for the **/**/** format, but when I type in something like 121415 it decides it will pick any month, day, and year it desires. Am I missing something here, I figure it should be pretty self explanatory, but clearly I'm not getting it.

 

[Jonmo1]

but when I type in something like 121415 it decides it will pick any month, day, and year it desires.

That's not how you enter a date in Excel.
You should enter the date with the /'s like 12/14/15

When you enter it like 121415, that's not really a date, Excel sees it as just a number (121 thousand 4 hundred fifteen)
Dates in Excel are really just numbers incrimenting by 1 since Jan 1 1900.
So 121415 is actually June 2nd of the year 2232 (the 121415th day since Jan 1 1900)
It's the cell formatting that makes it look like that date.

 

[Comfy]

If you type in 121415 Excel will return that many days from 00/01/1900 (dd/mm/yyyy)

So 02/06/2232 (dd/mm/yyyy)

You will have to type the "/" as well.

 

[blueboxdoctor]

That's not how you enter a date in Excel.
You should enter the date with the /'s like 12/14/15

When you enter it like 121415, that's not really a date, Excel sees it as just a number (121 thousand 4 hundred fifteen)
Dates in Excel are really just numbers incrimenting by 1 since Jan 1 1900.
So 121415 is actually June 2nd of the year 2232 (the 121415th day since Jan 1 1900)
It's the cell formatting that makes it look like that date.

Right, ok, so then what is the point of setting up the cell to format like a date if you still basically have to manually put it in as a date?

 

[Jonmo1]

Because there are many 'different' ways to format a date...

Do you want to see
12/14/15
or
Dec 14
or
Dec 14 2015
or
14/12/2015
or
Monday, December 14, 2015

etc...

 

[blueboxdoctor]

Because there are many 'different' ways to format a date...

Do you want to see
12/14/15
or
Dec 14
or
Dec 14 2015
or
14/12/2015
or
Monday, December 14, 2015

etc...

OK, right, so isn't that why I selected the option for 12/13/14? I assumed if I then inputted like 021415 it would know to split it up.