Format as eg "17th January"

AndyDDUK · #1 01-10-2013, 02:55 AM

Hi

I'm using an excel macro to format dates

I need to format as eg "17th January" or "2nd March" type format

Please help

Many thanks

JohnWilson · #2 01-10-2013, 03:34 AM

AFAIK Andy there's no built in way.
Try:

Sub getdate()
MsgBox getDay(Day(Now)) & " " & MonthName(Month(Now))
End Sub

Function getDay(dayNum As Long) As String
Select Case dayNum
Case Is = 1, 21, 31
getDay = dayNum & "st"
Case Is = 2, 22
getDay = dayNum & "nd"
Case Is = 3, 23
getDay = dayNum & "rd"
Case 4 To 30
getDay = dayNum & "th"
End Select
End Function

AndyDDUK · #3 01-10-2013, 04:05 AM

Many thanks

If I have my dates (eg 01/01/2012) in cell A1, how do i modify?

Cheers

JohnWilson · #4 01-10-2013, 04:14 AM

Are you in Excel (This is the PowerPoint section!)

Maybe:

Sub getdate()
Dim myDate As Date
myDate = ActiveSheet.Range("A1")
ActiveSheet.Range("A1") = getDay(Day(myDate)) & " " & MonthName(Month(myDate))
End Sub

Function getDay(dayNum As Long) As String
Select Case dayNum
Case Is = 1, 21, 31
getDay = dayNum & "st"
Case Is = 2, 22
getDay = dayNum & "nd"
Case Is = 3, 23
getDay = dayNum & "rd"
Case 4 To 30
getDay = dayNum & "th"
End Select
End Function

AndyDDUK · #5 01-10-2013, 04:38 AM

This works a treat thank you!

Sorry...yes I appear to be in the wrong forum

Have a good day

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to edit the "Format" and the "show level" of an EXISTING table of content?	Jamal NUMAN	Word	2	08-14-2011 10:46 AM
How to know the "format" of the "table of content"?	Jamal NUMAN	Word	2	07-08-2011 02:24 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM
Why the "table of contents" doesn't take the same format of the "headings"?	Jamal NUMAN	Word	5	06-15-2011 10:14 AM
"Format" tab has no "Picture"	Cara	Drawing and Graphics	2	05-20-2011 10:13 AM