[Terry Pinnell[_2_]]

In Excel 2000 I've used the Customise option
ddd dd mmm
to get my dates looking like this:
https://dl.dropboxusercontent.com/u/...teFormat-1.jpg

But those zeros look ugly. Can I remove them so that, for example, row
6 displays as 'Fri 1 Aug' please?

Even nicer would be 'Fri 1st Aug' or even 'Friday 1st August' but I
guess that's defintely impossible without VBA?

--
Terry, East Grinstead, UK

[Ron Rosenfeld[_2_]]

On Tue, 15 Jul 2014 08:56:05 +0100, Terry Pinnell wrote:

In Excel 2000 I've used the Customise option
ddd dd mmm
to get my dates looking like this:
https://dl.dropboxusercontent.com/u/...teFormat-1.jpg

But those zeros look ugly. Can I remove them so that, for example, row
6 displays as 'Fri 1 Aug' please?

ddd d mmm



Even nicer would be 'Fri 1st Aug' or even 'Friday 1st August' but I
guess that's defintely impossible without VBA?

Correct

[Terry Pinnell[_2_]]

Ron Rosenfeld wrote:

On Tue, 15 Jul 2014 08:56:05 +0100, Terry Pinnell wrote:

In Excel 2000 I've used the Customise option
ddd dd mmm
to get my dates looking like this:
https://dl.dropboxusercontent.com/u/...teFormat-1.jpg

But those zeros look ugly. Can I remove them so that, for example, row
6 displays as 'Fri 1 Aug' please?

ddd d mmm



Even nicer would be 'Fri 1st Aug' or even 'Friday 1st August' but I
guess that's defintely impossible without VBA?

Correct

Thanks Ron, appreciate the fast reply. So simple!

I've lost what little VBA knowhow I once had. But I've now written a
macro (with Macro Express Pro) that will take a block of text on the
clipboard in that 'raw' format and convert it to the civilised
'suffixed' form. (It actually turned out to be easier to do from the
'Friday 01 August' input rather than 'Friday 1 August'.)

--
Terry, East Grinstead, UK

[Ron Rosenfeld[_2_]]

On Tue, 15 Jul 2014 16:07:36 +0100, Terry Pinnell wrote:

Ron Rosenfeld wrote:

On Tue, 15 Jul 2014 08:56:05 +0100, Terry Pinnell wrote:

In Excel 2000 I've used the Customise option
ddd dd mmm
to get my dates looking like this:
https://dl.dropboxusercontent.com/u/...teFormat-1.jpg

But those zeros look ugly. Can I remove them so that, for example, row
6 displays as 'Fri 1 Aug' please?

ddd d mmm



Even nicer would be 'Fri 1st Aug' or even 'Friday 1st August' but I
guess that's defintely impossible without VBA?

Correct

Thanks Ron, appreciate the fast reply. So simple!

I've lost what little VBA knowhow I once had. But I've now written a
macro (with Macro Express Pro) that will take a block of text on the
clipboard in that 'raw' format and convert it to the civilised
'suffixed' form. (It actually turned out to be easier to do from the
'Friday 01 August' input rather than 'Friday 1 August'.)

Glad to help. Thanks for the feedback.

BTW, I err'd in indicating that VBA is REQUIRED to do the ordinal number formatting. In versions of Excel starting with 2007, conditional formatting includes number formats. So if that is the case, you could use conditional formatting to produce the result -- and you would still have a "real" date in the cell that could be used in calculations.

Set up the base or regular "number format" for the cell(s) as:

ddd d"th" mmm

Then enter three (3) conditional formats similar to:

=OR(DAY(A1)=3,DAY(A1)=23) Format As: ddd d"rd" mmm

=OR(DAY(A1)=2, DAY(A1)=22) Format As: ddd d"nd" mmm

=OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31) Format As: ddd d"st" mmm