Small Help - VBA Script

[Raja Kamarthi]

Need small help in getting a desired format through VBA script:


My code takes a value from a cell which is in date format and it gives the output in "1/8/2013" format.Below is the code:


"Your reporting day would be on" & "" & "" & Sheet1.Range("G" & (Cntr)).Value and the output for this is "Your reporting day would be on 1/8/2013"


Now I need the output in "8th January 2013" format.


Please share your thoughts.


Regards,

Raja

 

[MrBramme]

Hi,


I believe your best bet is to experiment with the format command.

[pre]

"Your reporting day would be on" & "" & "" & Format(Sheet1.Range("G" & (Cntr)).Value , "dd-mmm-yyyy")

[/pre]

please note "dd-mmm-yyyy" will not output your desired format, but i assume if you mess around with this you might find what you're looking for.

 

[Luke M]

Doesn't use the st, nd, rd, and th abbreviations after the numbers, but this gets pretty close:

myMsg = "Your reporting day would be on " & Format(Sheet1.Range("G" & (Cntr)).Value, "d mmmm yyyy")

 

[Raja Kamarthi]

@MrBramme:

You gave a shot which was pretty close, thanks for that


@Luke:

Many thanks, you got that absolutely perfect


Raja

 

[MrBramme]

Teamwork ... yaaaaay


no prob mate

 

[Debraj]

Hi Raja,


Just a little.. addition in Luke's Formula.. by adding st, nd, rd, and th abbreviations

[pre]

'cntr = 2
myMsg = "Your reporting day would be on " & Format(Sheet1.Range("G" & (cntr)).Value, "d") & _
Mid("thstndrdth", WorksheetFunction.Min(9, 2 * Right(Format(Sheet1.Range("G" & (cntr)).Value, "d"), 1) * _
IIf((Format(Sheet1.Range("G" & (cntr)).Value, "d") Mod 100) - 11 > 2, 1, 0) + 1), 2) _
& " " & Format(Sheet1.Range("G" & (cntr)).Value, "mmmm yyyy")

[/pre]

PS: @myself: Is it really little!!! I don't think so..


Regards,

Deb

 

[SirJB7]

Hi, Raja Kamarthi!


You can also try this code:

-----

[pre]

Dim sDate As String
sDate = Format(Range("G" & (Cntr)).Value, "dd mmmm yyyy")
MsgBox "Your reporting day would be on " & _
Left$(sDate, InStr(sDate, " ") - 1) & _
Mid$("th st nd rd st nd rd st ", _
InStr("0001_02_03_21_22_23_31_", Left$(sDate, 2) & "_") + 1, _
3) & _
Right$(sDate, Len(sDate) - InStr(sDate, " "))

[/pre]
-----


Just advise if any issue.


Regards!

 

[Luke M]

for more reading on using ordinal numbers, I'd recommend here:

http://www.cpearson.com/Excel/ordinal.aspx

 

[SirJB7]

@Luke M

Hi!

Read, but personally I still stick to my formula. I like it, don't you? Say yes, otherwise tonight I wouldn't be able to get to sleep

Regards!