Convert input data

Jack in the UK · Feb 21st, 2002, 04:02 AM

Hi guys playing with :

If i input 21 say as today is 21st Feb 2002 i need to convert to Correct 21st Feb 2002

All i will input is 21??? any ideas guys rather than have 21 in one column and next column converts.

Cheers guys

Rdgs
=========
Jack

Derek · Feb 21st, 2002, 04:11 AM

Jack
Custom format cells #"st Feb 2002"
Is that what you mean?
Derek

Juan Pablo González · Feb 21st, 2002, 05:26 AM

Or use:

DateValue(Year(Date),Month(Date),MyInput+0)

Jack in the UK · Feb 21st, 2002, 05:33 AM

Cheers guys...

big thanks

Rgds
===========
Jack

Drew · Feb 21st, 2002, 08:51 AM

Jack,
You could try...
=DAY(C8)&IF(INT(MOD(DAY(C8),100)/10)=1,"th",IF(MOD(DAY(C8),10)=1,"st",IF(MOD(DAY(C8),10)=2,"nd",IF(MOD(DAY(C8),10)=3,"rd","th"))))&" "&TEXT(C8,"mmm yyyy")
where c8 houses a date but the result becomes text.

HTH,
Drew
FYI the above comes from J. Walkenbach. Someday I'll get there!

Feb 21st, 2002, 05:26 AM	#3
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	Or use: DateValue(Year(Date),Month(Date),MyInput+0) __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 21st, 2002, 04:02 AM	#1
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	Hi guys playing with : If i input 21 say as today is 21st Feb 2002 i need to convert to Correct 21st Feb 2002 All i will input is 21??? any ideas guys rather than have 21 in one column and next column converts. Cheers guys Rdgs ========= Jack

Feb 21st, 2002, 04:11 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Jack Custom format cells #"st Feb 2002" Is that what you mean? Derek

Feb 21st, 2002, 05:33 AM	#4
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,064	Cheers guys... big thanks Rgds =========== Jack

Feb 21st, 2002, 08:51 AM	#5
Drew Board Regular Join Date: Feb 2002 Location: Boston, Mass Posts: 169	Jack, You could try... =DAY(C8)&IF(INT(MOD(DAY(C8),100)/10)=1,"th",IF(MOD(DAY(C8),10)=1,"st",IF(MOD(DAY(C8),10)=2,"nd",IF(MOD(DAY(C8),10)=3,"rd","th"))))&" "&TEXT(C8,"mmm yyyy") where c8 houses a date but the result becomes text. HTH, Drew FYI the above comes from J. Walkenbach. Someday I'll get there!