Ordinals in date field

[kestrel1306]

Hi Team VBA-Express,

I have a date field that uses spin boxes to go up and down in time using the dddd dd mm yyyy format which gives Monday 01 January 2018 in a text field.

I want to place ordinals (st, nd, rd, th) in their so tried UserForm8.text_01 = Replace(UserForm8.text_01, "01", "1st")

Which works (almost) perfectly..

In this example I end up with Monday 1st January 21st8

As you can see, the 01 in the year is also getting converted to 1st.

Simple question then is how do I limit the replace function to seeing only 01 (day) and not the 01 in 2018 (the year) please?


Thanks
Kes

[SamT]

I don't do Word, so you'll have to figure out the correct VBA Method or Statement. This could work in Excel.


Select Case Right(Day(Date), 1)
Case 1: ORD = "st"
Case 2: ORD = "nd"
Case 3: ORD = "rd"
Case else: ORD = "th"
End select

Format(Date, "dddd dORD mmmm yyyy")

[macropod]

Try:

Dim Dt As String
Dt = UserForm8.text_01
UserForm8.text_01 = Split(Dt, " ")(0) & " " & Ordinal(CLng(Split(Dt, " ")(1))) & " " & Split(Dt, " ")(2) & " " & Split(Dt, " ")(3)
with:

Function Ordinal(Val As Long) As String
Dim strOrd As String
If (Val Mod 100) < 11 Or (Val Mod 100) > 13 Then strOrd = Choose(Val Mod 10, "st", "nd", "rd") & ""
Ordinal = Val & IIf(strOrd = "", "th", strOrd)
End Function
Note: Something slightly simpler (calling the same function) could be used if you had a comma after the Monday, for example:

Dim DtVal As Date
DtVal = CDate(Split(UserForm8.text_01, ",")(1))
UserForm8.text_01 = Format(DtVal, "DDDD, ") & Ordinal(Day(DtVal)) & Format(CDate(Split(Dt, ",")(1)), " MMMM YYYY")

[kestrel1306]

Hi macropod & SamT,

Thanks very much for your replies.

SamT that gave an error, and macropod, it went a tad over my head...

I had an epiphany last though and put in

UserForm8.text_01 = Replace(UserForm8.text_01, "21st8", "2018")

Which fixed it. Whilst I know it's nowhere near as elegant as your solutions, it worked which is most important for me.

Thank you very much for your help :-)


Regards
Kes

[macropod]

and macropod, it went a tad over my head...
All you needed to do was to replace your:

UserForm8.text_01 = Replace(UserForm8.text_01, "01", "1st")
with the first or third pieces of code I posted, as appropriate, and add the function (the second piece of code I posted) to your code module.

[SamT]

I would rather give hints than write your code for you.

Teach a man VBA and he will code forever. Write his code and he'll be back for more of the same.

[macropod]

I would rather give hints than write your code for you.
That's all very well, but your 'hint' code wouldn't work in Excel, either - there is no 11st, 12nd or 13rd of the month...

[kestrel1306]

Hi Guys,

I hear you with regard to guided discovery of coding.

As you can tell I'm a google-based amateur so sometimes I need more help, than other times :-)

all help appreciated.


Regards
Kes

[macropod]

That's OK. Study the code and learn from it. Don't become one of those who invests little effort learning how to do their own coding. Such people soon find their support circle diminishing...

[kestrel1306]

Macropod,

I hear ya, don't want to burn bridges etc.

My google-fu is reasonably strong, my app is used by a few thousand folk with less and less things that make it go boom... :-)


Kes