Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Add st and th to dates
Hi All,
Is there a way in excel to format a date so that it automatically includes the "th" and "st". I.e. so 1/8/05 -- 1st August 2005? Ta Andi |
#2
|
|||
|
|||
Hello-
Excel provides no such date formatting, and trying to create a custom format like that would be impossible due to the variable 'st', 'rd', 'th', etc. & when each should be used. Perhaps it can be accomplished with VBA, but unless someone has it available & is willing to share, it would probably be more trouble to write than its worth... Unless you need to use the dates for calculations. Regards |:) "Andibevan" wrote: Hi All, Is there a way in excel to format a date so that it automatically includes the "th" and "st". I.e. so 1/8/05 -- 1st August 2005? Ta Andi |
#3
|
|||
|
|||
Andi,
Chip Pearson can get you started... http://www.cpearson.com/excel/ordinal.htm Jim Cone San Francisco, USA "Andibevan" wrote in message ... Hi All, Is there a way in excel to format a date so that it automatically includes the "th" and "st". I.e. so 1/8/05 -- 1st August 2005? Ta Andi |
#4
|
|||
|
|||
It's a little chunky, but this works:
=(IF(MOD((DAY(A1)),10)=1,DAY(A1)&"st",IF(MOD((DAY( A1)),10)=2,DAY(A1)&"nd",IF(MOD((DAY(A1)),10)=3,DAY (A1)&"rd",DAY(A1)&"th"))))&TEXT(A1," mmmm ")&YEAR(A1) .... where your date is in A1. |
#5
|
|||
|
|||
Oops! The previously formula fails for the 11th, 12, and 13th. Use
this instead: =(IF(INT(DAY(A1)/10)=1,DAY(A1)&"th",IF(MOD((DAY(A1)),10)=1,DAY(A1)& "st",IF(MOD((DAY(A1)),10)=2,DAY(A1)&"nd",IF(MOD((D AY(A1)),10)=3,DAY(A1)&"rd",DAY(A1)&"th")))))&TEXT( A1," mmmm ")&YEAR(A1) |
#6
|
|||
|
|||
Nicely done Dave........
Vaya con Dios, Chuck, CABGx3 "Dave O" wrote: Oops! The previously formula fails for the 11th, 12, and 13th. Use this instead: =(IF(INT(DAY(A1)/10)=1,DAY(A1)&"th",IF(MOD((DAY(A1)),10)=1,DAY(A1)& "st",IF(MOD((DAY(A1)),10)=2,DAY(A1)&"nd",IF(MOD((D AY(A1)),10)=3,DAY(A1)&"rd",DAY(A1)&"th")))))&TEXT( A1," mmmm ")&YEAR(A1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default Dates | Excel Discussion (Misc queries) | |||
Using dates for x-axis values as string instead of creating a scale | Charts and Charting in Excel | |||
Excel not recognizing Dates properly. | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel |