Format date in excel 2000 like '31st January 2005'

[Guest]

Trying to set up field in Excel Data Source file so that the current date can
be input in letters typed in this format '31st January 2005'

Is it possible or am I stuck with dd/mmm/yy format?

 

[Ron Rosenfeld]

Trying to set up field in Excel Data Source file so that the current date can
be input in letters typed in this format '31st January 2005'

Is it possible or am I stuck with dd/mmm/yy format?

You can certainly type in or input the date in that format. It will be
interpreted as TEXT by Excel.

Perhaps if you explained your problem in more detail ???

Entering a date in that format seems rather inefficient compared to other
methods.
--ron

 

[JE McGimpsey]

You can type in

31 January 2005

and display the same format : dd mmmm yyyy

but you can't use the ordinal to input data without using a VBA event
macro.

 

[Flintstone]

Hello Malcolm:

I don’t know if this will work with Excel 2000 but try this.

The date goes in cell A1 and the day ( =DAY(A1) ) goes in cell A2.

=A1&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&
"&TEXT(A2,"mmmm yyyy")

I got this from this web site listed under excel tips.

I hope this helps.

Mat

 

[Paul D. Simon]

Hi Matt,

Just a slight alteration to your formula that will also eliminate the
need for A2. (It may show below as wrapped to several lines.
Obviously, you'd enter it all on 1 line.)


=DAY(A1)&IF(OR(AND(MOD(DAY(A1),100)>=4,MOD(DAY(A1),100)
<=20),AND(MOD(DAY(A1),100)>=24,MOD(DAY(A1),100)<=30)),"th",
CHOOSE(MOD(DAY(A1),10),"st","nd","rd","th","th","th","th","th","th"))&"
"&TEXT(A1,"mmmm yyyy")


Paul