Date Format

[Guest]

I would like Excel and Access to display dates in the following format:
Wednesday 3rd August 2005

Currently, I can only get a display of :
Wednesday 3 August 2005 using dddd d mmmm yyyy

Grateful for help getting 3rd instead of 3 displayed.

Many thanks

Stuart

 

[Karthik]

Hi

There is no such format in Excel. And you will have to write a custom
function that gives you the date in the format you want.
Here is a solution but this solution will work only for your current
requirement.

Right click the cell where u have ur date select format and enter this
as a custom format.

d"rd" mmmm yyyy

But this will not work for say for 2nd or 4th and you will have to
change the custom format.

Thanks
Karthik Bhat
Bangalore

 

[Dave Peterson]

Maybe you could use a helper cell with a formula.

Chip Pearson has one at:
http://www.cpearson.com/excel/ordinal.htm

If A1 contains the date:
=TEXT(A1,"DDDD ") & DAY(A1)
&IF(AND(MOD(DAY(A1),100)>=10,MOD(DAY(A1),100)<=14),"th",
CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
& TEXT(A1," MMMM YYYY")

All one cell.

 

[Guest]

Thanks Dave, this works great for excel.

Any ideas how I'd get Access to accept this formula?

Thanks

Stuart

 

[Dave Peterson]

No idea at all.

You may want to post in one of the Access newsgroups.

 

[Guest]

Will do.

Thanks again.

Stuart

No idea at all.

You may want to post in one of the Access newsgroups.

 

[Guest]

I'm trying to modify the code you helped locate to show only the ordinal
number from the date, not the month, year, day of week, etc. Any ideas how
to incorporate this in?

 

[Guest]

nevermind. I got it playing around deleting different parts and this worked:

=TEXT(D37,"") & DAY(D37)
&IF(AND(MOD(DAY(D37),100)>=10,MOD(DAY(D37),100)<=14),"th",

CHOOSE(MOD(DAY(D37),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))