Date Format

[StuartTheBrit]

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.

StuartTheBrit wrote:
>
> 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

--

Dave Peterson

[StuartTheBrit]

Thanks Dave, this works great for excel.

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

Thanks

Stuart



"Dave Peterson" wrote:

> 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.
>
> StuartTheBrit wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

[Dave Peterson]

No idea at all.

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

StuartTheBrit wrote:
>
> Thanks Dave, this works great for excel.
>
> Any ideas how I'd get Access to accept this formula?
>
> Thanks
>
> Stuart
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > StuartTheBrit wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[StuartTheBrit]

Will do.

Thanks again.

Stuart



"Dave Peterson" wrote:

> No idea at all.
>
> You may want to post in one of the Access newsgroups.
>
> StuartTheBrit wrote:
> >
> > Thanks Dave, this works great for excel.
> >
> > Any ideas how I'd get Access to accept this formula?
> >
> > Thanks
> >
> > Stuart
> >
> > "Dave Peterson" wrote:
> >
> > > 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.
> > >
> > > StuartTheBrit wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[adodson]

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?

"Dave Peterson" wrote:

> 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.
>
> StuartTheBrit wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

[adodson]

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"))

"adodson" wrote:

> 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?
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > StuartTheBrit wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >