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

--
Message posted via http://www.officekb.com

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

"enrico via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

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



"enrico via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

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)



"enrico via OfficeKB.com" wrote:

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

--
Message posted via http://www.officekb.com

=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 (MVP - Excel)


"enrico via OfficeKB.com" <u41845@uwe wrote in message
news:961afee85525c@uwe...
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.)

--
Message posted via http://www.officekb.com

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)


"Rick Rothstein" wrote in message
...
=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 (MVP - Excel)


"enrico via OfficeKB.com" <u41845@uwe wrote in message
news:961afee85525c@uwe...
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.)

--
Message posted via http://www.officekb.com

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)


"Rick Rothstein" wrote in message
...
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)


"Rick Rothstein" wrote in message
...
=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 (MVP - Excel)


"enrico via OfficeKB.com" <u41845@uwe wrote in message
news:961afee85525c@uwe...
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.)

--
Message posted via http://www.officekb.com

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

On Fri, 15 May 2009 08:18:31 GMT, "enrico via OfficeKB.com" <u41845@uwe
wrote:

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

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Fri, 15 May 2009 08:18:31 GMT, "enrico via OfficeKB.com" <u41845@uwe
wrote:

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

Thanks Rick.

Not something you see every day.

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


Gord

On Sat, 16 May 2009 15:13:55 -0400, "Rick Rothstein"
wrote:

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

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

--
Message posted via http://www.officekb.com

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

--
Rick (MVP - Excel)


"enrico via OfficeKB.com" <u41845@uwe wrote in message
news:964b0a7d8dc2a@uwe...
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

--
Message posted via http://www.officekb.com