Is there a way of putting the 'st', 'rd' and 'th' after the date?

**AndyRoo119** · 05-20-2013, 02:33 PM

Hi there,
I'm fairly new to Excel (2003) but nevertheless have been asked to produce what could be described as a 'page a day' diary with different actions to be carried out each day of the week. Each day of the week has various tasks to be completed and are similar every Monday, Tuesday etc.
I have produced a weeks worth of pages (worksheets) and have been able to format a cell, actually D2, to produce for example 'Monday 1'.
What I would like it to say though is 'Monday 1st' then the next page 'Tuesday 2nd' and so on.
Is there an easy way to do this or am I barking up the wrong tree altogether?
I would appreciate any help as I feel I'm a bit out of my depth.

Thanks

**Lemice** · 05-20-2013, 02:42 PM

Hello and welcome to the forum,

Assuming you have "Monday 1" in cell D2, you can paste this formula into cell E2 (right next to it)
=D2&LOOKUP(RIGHT(D2,1)*1,{0,1,2,3,4},{"th","st","nd","rd","th"})

**AndyRoo119** · 05-21-2013, 07:41 AM

Hi again Lemice,

Just been looking through the forum pages and noticed one or two people uploaded examples
of their sheets to make it easier for "the experts" to see whats going on.

Attached - hopefully, is an example of part of my diary pages, hope it makes things clearer.

As you can see I am hoping to put the "rd" and the "th" in the appropriate cells.

Thanks once again

Drew

Drews Date calc sheet.xls

**daddylonglegs** · 05-21-2013, 08:22 AM

Originally Posted by Lemice

=D2&LOOKUP(RIGHT(D2,1)*1,{0,1,2,3,4},{"th","st","nd","rd","th"})

but that wouldn't give the correct suffix for 11th, 12th or 13th........

Try this formula in O4

=LOOKUP(DAY(D4),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})

**ChemistB** · 05-21-2013, 08:55 AM

I suggest, that you hide a row or column in each sheet and put your =sheet1!D4+1 there (no need for formatting) as it's hidden anyway. Let's say it's in A1

Then in a visible cell
=TEXT(A1, "dddd d")&LOOKUP(DAY(D4+1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})
See attachment