On Fri, 18 Mar 2005 02:19:13 -0800, David wrote:

Using XL2K:
A2 contains text 'From: Feb 16th'
C2 contains text 'To: Feb 28th 2005'

For alignment purposes I don't want to split to separate cells, but would
like to programatically increase the dates to next bi-monthly period AND
use proper ordinals 'st' or 'th' in the result.

Am I asking too much?

I'm not sure exactly what you want. But with a date (real excel date, not a
text string) in A2, the following Sub will increase the date by two weeks; put
the ending date in C2; and format the date as you describe.

You may have to change some of the details to suit your needs.

========================================
Sub BiWeeklyDt()
Dim rg1 As Range, rg2 As Range
Dim dy As Long
Dim Suffix As String
Dim Fmt As String

Const Quote As String = """"

Set rg1 = Range("A2")
Set rg2 = Range("C2")

If IsDate(rg1.Value) Then
rg1.Value = rg1.Value + 14
rg2.Value = rg1.Value + 13
dy = Day(rg1.Value)
Suffix = Quote & OrdinalSuffix(dy) & Quote
Fmt = """From: """ & "mmm d" & Suffix
rg1.NumberFormat = Fmt

dy = Day(rg2.Value)
Suffix = Quote & OrdinalSuffix(dy) & Quote
Fmt = """To: """ & "mmm d" & Suffix & " yyyy"
rg2.NumberFormat = Fmt

End If

End Sub

Function OrdinalSuffix(Num) As String
Dim Suffix As String

If Not IsNumeric(Num) Then Exit Function
If Num < Int(Num) Then Exit Function

Select Case Num Mod 10
Case Is = 1
Suffix = "st"
Case Is = 2
Suffix = "nd"
Case Is = 3
Suffix = "rd"
Case Else
Suffix = "th"
End Select

Select Case Num Mod 100
Case 11 To 19
Suffix = "th"
End Select

OrdinalSuffix = Suffix
End Function
===============================


--ron