Changing date format not working no matter what I try

The dates in a column look like this: 06/03/18 (ie, June 3, 2018)

I would like to change the formatting to 18/06/30 (ie, 2018 June 3) or even 18-06-03, whatever is doable.

I've tried the various 'Format Cells...' options, including the custom options, but the dates never change. I've Googled for solutions but the ones offered aren't working for me.

Now I've become sad on a Sunday afternoon. Can anyone make me smile again?

that sounds a lot like the cell values look like dates but are text

if the dates are in column A try datevalue(A1) in column B or any other empty column to see if that will convert to a number that can be reformatted in your desired date format

Try

Highlight column with Dates ==>Data ==>Text to Columns ==Step 3 ==> select DMY==>Finish

If the dates are real dates check for conditional formatting

Thanks for all the input (pun intended ). I'm not very familiar with formulas and functions, etc., so the above will take some self-education. In the meantime, however, the main problem is that I can't get the column to stop being text. I try the following:

1. Right click a cell
2. choose 'Format Cells...'
3. choose the Date option
4. select the desired format from the list
5. click OK.

Nothing changes. Even if I double click the cell and hit Enter (a suggestion from somewhere else), no change.

1. Right click a cell
2. choose 'Format Cells...'
3. choose the Custom option
4. select the desired format from the list OR type one in myself
5. click OK.

Still no change.

I've tried to set the format in an unused cell, but can't do it there, either.

I did try using the formulas you all mentioned, Googling how to use them, but not getting the hang of it/not fully understanding how they work. I have 125 rows to edit, so doing them manually is not ideal.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by JohnTopley

Try

Highlight column with Dates ==>Data ==>Text to Columns ==Step 3 ==> select DMY==>Finish

This worked for the majority of the dates, and had to manually change the rest. Thank you.

Originally Posted by MarvinP

Hi Joel,

Try this:
say your TextDates are in column D. In a new blank column starting at the top date row, =DateValue(D1) and press enter. Then grab the bottom right corner of this cell and pull it down for all the rows you have your TextDate. THEN - Copy that formula column and PASTE it OVER those dates using "Values ONLY". Then you can delete the column you just created.

Hope you can do these steps.

Followed the steps exactly, and got the following result (with March 17, 2009 being some random date unrelated to the data I have), which means I either still did something is seriously wrong with that column! lol

Please Login or Register  to view this content.

Originally Posted by FDibbins

If you tried the test from post #3 and got a FALSE answer, then you have a TEXT date and need to convert it.
1 way (if you have a lot of dates) is to use the suggestion in post #4. If you only have 2 or 3, just retype them (make sure to change the format 1st though)

Aha yes, I looked at post #4 again, which was slightly cryptic but I figured it out, and got the dates changed with some manual changing needed. Thanks!