how to change how a column of dates look

BlueJohn007 wrote:

I am still unable to change the look of the column contains dates from:

10/1/19

10/2/19

10/3/19

to:

1st Oct 2019

2nd Oct 2019

3rd Oct 2019

The Custom Format works here.

The Custom Format works here. I suggest trying again with those cells, perhaps reentering the date-time in them.

Note that mm/dd/yyyy is the default if your region (at File > Advanced > Language & Region) is set to United States. If you set the region to, say, the United Kingdom (probably other regions too too) your preferred format becomes one of the choices under Data Format in Cell panel at the right.

SG

Hi BlueJohn,

It is not clear from your post what you did. Did you apply a custom format as suggested? What is the current format of those cells? Is the data we see entered by you or a result of a formula?

quinn

~As a side note, it looks like you have merged cells in the first 4 rows and several columns. While these are not causing the misbehavior with the formatting they are likely to cause you difficulties in the future. You can get the same look using the text tools and hiding cell borders.

You’ve already received advice for how to change it to 1 Oct, 2019, but were you looking specifically to change it to 1st Oct, 2019? That is possible, but not with formats. You’ll have to use text formulas to convert a date value to a custom text string. Is that what you’re looking for, or is 1 Oct, 2019 fine?

Sure, I can look at it.

Hi BlueJohn,

In the Cell> Data Format sidebar popup drag down to Create Custom Format.

You should be able to approximat waht you want.

quinn

Ok, BlueJohn,

I don't know how to expand on my instructions above without knowing where your difficulty is in following them. I thought they were complete and clear.

quinn