customize date

[enrico via OfficeKB.com]

i customized a cell with a now() date field so that it's date is always
updated. i also designed the cell and add a "th" word so that today would be
shown like this: 15th. what i miss is that the first to third day doesn't end
up with "th" but rather "st", "nd" and "rd" respectively. how will i
customize it that it will be flexible according to the date? (e.g. 1st, 2nd,
3rd, 4th, etc.)

 

[Mike H]

Hi,


With a date in A1 use this

=DAY(A1)&IF(OR(DAY(A1)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(A1),1),"st
","nd ","rd "),"th ")&TEXT(A1,"mmmm, yyyy")

Mike

 

[Teethless mama]

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

 

[Teethless mama]

Ignore my previous post
Try this one:

=TEXT(A1,"mmmm
")&DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&", "&YEAR(A1)

 

[Rick Rothstein]

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

Note: The basic concept behind this formula was mine, but Biff (T. Valko)
tightened it up to produce this amazingly short formula.

 

[Rick Rothstein]

Of course, you wanted more formatting than I gave you; try this instead...

=TEXT(A1,"mmmm
")&DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(A1-11,100)>2)+1),2)&",
"&YEAR(A1)

--
Rick (MVP - Excel)

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

Note: The basic concept behind this formula was mine, but Biff (T. Valko)
tightened it up to produce this amazingly short formula.

 

[Rick Rothstein]

This one's a little bit shorter (and one function call less)...

=TEXT(A1,"mmmm d")&MID("thstndrdth",MIN(9,2*RIGHT(
DAY(A1))*(MOD(A1-11,100)>2)+1),2)&", "&YEAR(A1)

--
Rick (MVP - Excel)

Of course, you wanted more formatting than I gave you; try this instead...

=TEXT(A1,"mmmm
")&DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(A1-11,100)>2)+1),2)&",
"&YEAR(A1)

 

[Gord Dibben]

If you don't want to use a helper cell as others have suggested you could
use event code to change in-cell.

Code below returns 16th May, 2009 when the date is entered. Adjust to
suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For Each cell In Target
If IsDate(cell.Value) Then
Select Case Day(cell.Value)
Case 1, 21, 31
cell.NumberFormat = "d""st"" mmm, yyyy"
Case 2, 22
cell.NumberFormat = "d""nd"" mmm, yyyy"
Case 3, 23
cell.NumberFormat = "d""rd"" mmm, yyyy"
Case Else
cell.NumberFormat = "d""th"" mmm, yyyy"
End Select
End If
Next cell
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

While just a *little* bit more obfuscated <g>, the OP (and others reading
this thread) might be interested in seeing this one-liner assignment
statement version of your code...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
For Each Cell In Target
If IsDate(Cell.Value) Then Cell.NumberFormat = "d""" & _
Mid$("thstndrdthththththth", 1 - 2 * (Day(Cell.Value) Mod 10) * _
(Abs(Day(Cell.Value) Mod 100 - 12) > 1), 2) & """ mmm, yyyy"
Next
End Sub

 

[Gord Dibben]

Thanks Rick.

Not something you see every day.

I'll keep it and someday work through it........maybe<g>


Gord

 

[enrico via OfficeKB.com]

what if your cell is updated, like the date would be today(=now( ))? i
believe the formulas are only good on which you will give the data

 

[Rick Rothstein]

Instead of putting =Now() into the cell, put this formula in the cell
instead...

=DAY(NOW())&MID("thstndrdth",MIN(9,2*RIGHT(DAY(NOW()))*(MOD
(NOW()-11,100)>2)+1),2)&TEXT(NOW()," mmmm")&", "&YEAR(NOW())