Date Format

[Greg B]

I am wondering how I can make the vba to add the extensions to the date i.e.
24 to go to the 24th
23 to 23rd
etc
Thanks
here is the code I am using
MAIN.Caption = Format(Now, "hh:mm:ss" & " " & "dddd dd mmmm yyyy")

 

[SeanC UK]

Hi Greg,

I don't believe you can do this using Format, however you can use a simple
workaround. One thing to mention though is that once you use the ordinal
version of the number then the date will not be recognised as a date by
Excel. So if you are only using your routine to display a date then it is
fine, if you wish to use this date in Excel then you're better off keeping in
the format you already have.

OK, to do what you are asking you need to test the final digit of the day
and then append the characters as necessary. I have used the Format function
twice (either side of the added characters) because otherwise these
characters will be used in the formatting - 4th will become 4t and the h will
display the hour, st, nd and rd also use characters from the Format function,
so the double Format usage is necessary for all.

Select Case Right(Format(Now, "dd"), 1)
Case 1
MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "st " & Format(Now, "mmmm
yyyy")
Case 2
MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "nd " & Format(Now, "mmmm
yyyy")
Case 3
MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "rd " & Format(Now, "mmmm
yyyy")
Case Else
MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "th " & Format(Now, "mmmm
yyyy")
End Select

I hope this helps.

Sean.

 

[Greg B]

Thanks SeanC
Absolutely perfect for what i needed.

Thanks Once Again

Greg