Date Formatting Issue

[ardanni90]

Hello all,

I am having an issue formatting a cell that contains data from a .txt file into a new date format.

The cell shows data as 151201 and the format is "general." When I change the cell format to any date format, I get the date as 12/21/2313. I need it to say 12/01/2015.

Can any one please assist?

Thanks!

 

[jeffreybrown]

Hi Adam & Welcome to the Forum,

I believe you will need a formula to convert this

=(MID(A1,3,2)&"/"&RIGHT(A1,2)&"/"&20&LEFT(A1,2))+0

then custom format to the date format you desire

 

[JayStax]

The data there reflects the date 12/21/2313. There is no getting around that... What does the text file say?

Also how do you know the date is suppose to be 12/01/2015?

 

[Rick Rothstein]

I am having an issue formatting a cell that contains data from a .txt file into a new date format.

The cell shows data as 151201 and the format is "general." When I change the cell format to any date format, I get the date as 12/21/2313. I need it to say 12/01/2015.

Your "dates" are not real dates. Since it would involve physically changing the underlying value in the cell, you cannot make the change you want using Cell Formatting. Try this instead... select the column with your "dates", call up the Text To Columns dialog box (Data tab, Data Tools panel), click the Next button twice (to get to Step 3 of 3)... select the Date option button (upper left section of dialog page) and set the dropdown control next to it to YMD and then click the Finish button... you cells should now contain real dates that you can format with any date format you want.

 

[jeffreybrown]

Rats, you did it to me again Rick...

 

[ardanni90]

Jeff's formula worked perfectly for me!

Thanks!

 

[Rick Rothstein]

Jeff's formula worked perfectly for me!

You originally asked about using Cell Formatting which indicated you wanted to change the values directly in place... is that not what you really wanted? If it is, then did you see what I posted in Message #4?

 

[ardanni90]

It was, but when Jeff posted his formula, I applied it to my file and was able to get the result I was looking for. I appreciate your help too though!