Jump to content
Macro Express Forums

Converting day of month

Recommended Posts

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






(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


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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.



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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...