Converting day of month

[terrypin]

In my aging Excel 2000 I frequently enter a column of dates like this:

 

28/7/14

29/7/14

etc

 

(Of course, that's using the logical not the USA style! )

 

It's easily formatted to look like this:

 

 

I find those leading zeros ugly, although I can quickly remove them in my text editor with a simple global replacement. However I would ideally like instead to see

 

Friday 1st August
Saturday 2nd August
etc

 

Before I start coding, I wonder if anyone already has or knows of a macro to do it please?

 

Needless to say, it's arguable whether it's worth the effort. My list of dates is never very long and I could manually add the suffixes in a couple of minutes. But where's the fun in that?

 

--
Terry, East Grinstead, UK

[terrypin]

I realised a short time after posting that (using a blunt approach) the coding would be trivial. Here's the macro in case it's of use to anyone else:

 

--
Terry, East Grinstead, UK

DaySuffix.mex

[amonaghan]

Terry, an easier way would be to just change the format in Excel from 'dddd dd mmmm' to 'dddd d mmmm'. I use 2007, but assume this will work in 2000 too.

 

Alan

[acantor]

Terry, do you know about Excel's fill commands? Basically, you type periodic data in two contiguous cells; select the two cells; then drag the "handle" in the lower right corner of the selected cells in the direction you want to fill.

 

Fill commands work with integers and decimals (positive and negative, days of the week, months, dates (although not every format is supported), prices, and more.

 

For example: to create a row with cells starting at 01-January-2014 and going to 31-December-2014...

 

1. Type 01-January-2014 in a cell.

2. Type 02-January-2014 in the adjacent cell to the right.

3. Select the two cells.

4. With the mouse, grab the handle in the lower right corner, and drag right. This will cause cells to fill with values until the mouse is released.

[terrypin]

Terry, an easier way would be to just change the format in Excel from 'dddd dd mmmm' to 'dddd d mmmm'. I use 2007, but assume this will work in 2000 too.

 

Alan

Thanks Alan, yes, I discovered that single 'd' syntax too a little later. But adding correct suffixes was my real objective, something that even later versions of Excel can't manage!

 

Terry, East Grinstead, UK

[terrypin]

Terry, do you know about Excel's fill commands?

Thanks Alan, yes, I do use that feature, when my set of numbers or dates has no gaps.

 

Terry, East Grinstead, UK

[amonaghan]

Terry,

 

You could try this. I haven't tried it yet myself yet.

 

http://www.mrexcel.com/forum/excel-questions/285128-st-nd-rd-th-format-date.html#post1400910

 

OR THIS

 

http://www.cpearson.com/excel/Ordinal.aspx

 

Good luck.

 

Alan Monaghan

[terrypin]

Thanks Alan. Looks like I was wrong about Excel's capabilities!

 

Both tested and work OK, although I haven't yet successfully reorganised them to meet my immediate aim.

 

Academic now, of course, as my macro did the job neatly.

 

--
Terry, East Grinstead, UK