Cardinal (nth) date formats in Excel

[cfdh_edmundo]

Hi,

I was wondering if there was a date format in Excel which would allow a cardinal date to be displayed?

eg as follows:-

In cell D7 I have 01/01/2005

then in a cell I have the following formula:-

=TEXT(D7,"dddd dd mmmm, yyyy")

which returns :-

Saturday 01 January, 2005

is there any format which can return Saturday 01st January, 2005?

I searched in the online help but couldnt find much here

 

[DominicB]

Good afternoon cfdh_edmundo

Excel doesn't do ordinal numbers. Or rather can't do them unaided - Excel does provide the flexability to allow the knowledgable user to come up with a workaround.

Chip Pearson discusses this very issue on his pages here:

http://www.cpearson.com/excel/ordinal.htm

HTH

DominicB

 

[just_jon]

Welcome to MrExcel -- one way

Book1
	A	B	C	D	E
1	Tuesday 15th November, 2005	11/15/2005	Tuesday 15th November, 2005	1	st
2	Monday 14th November, 2005	11/14/2005	Monday 14th November, 2005	2	nd
3	Sunday 13th November, 2005	11/13/2005	Sunday 13th November, 2005	3	rd
4	Saturday 12th November, 2005	11/12/2005	Saturday 12th November, 2005	4	th
5	Friday 11th November, 2005	11/11/2005	Friday 11th November, 2005	5	th
6	Thursday 10th November, 2005	11/10/2005	Thursday 10th November, 2005	6	th
7	Wednesday 09th November, 2005	11/9/2005	Wednesday 09th November, 2005	7	th
8	Tuesday 08th November, 2005	11/8/2005	Tuesday 08th November, 2005	8	th
9	Monday 07th November, 2005	11/7/2005	Monday 07th November, 2005	9	th
10	Sunday 06th November, 2005	11/6/2005	Sunday 06th November, 2005	10	th
11	Saturday 05th November, 2005	11/5/2005	Saturday 05th November, 2005	11	th
12	Friday 04th November, 2005	11/4/2005	Friday 04th November, 2005	12	th
13	Thursday 03th November, 2005	11/3/2005	Thursday 03rd November, 2005	13	th
14	Wednesday 02nd November, 2005	11/2/2005	Wednesday 02nd November, 2005	14	th
15	Tuesday 01st November, 2005	11/1/2005	Tuesday 01st November, 2005	15	th
16				16	th
17				17	th
Sheet1

 

[cfdh_edmundo]

thanks guys thats great !