Display 08/26/2015 as 26th Aug 2015

[ThrottleWorks]

Hi,

I have a macro, which takes a date value from user through Input Box.

I need to convert date into following string.

For example if the date is 08/26/2015 I need to convert it as 26th Aug 2015.
If the date is 09/01/2015 I need to convert it is 1st Sep 2015.

I do not understand how do I get "th" or "st" part.

Can anyone please help me in this.

 

[Marc L]

Hi !

Maybe like this :​

Function EuroDateEn$(V)
    If IsDate(V) Then
                AR = Split("th st nd rd")
                D% = Day(V)
        EuroDateEn = D & AR(IIf(D > 3, 0, D)) & Format$(V, " mmm yyyy")
    End If
End Function

Sub Demo()
    MsgBox EuroDateEn(#9/1/2015#)
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[NARAYANK991]

Hi Marc ,

Try your code with a date such as January 21, 2015.

Narayan

 

[Marc L]

Ok but OP date entries are like 09/01/2015 …

Other way with same "date type" :​

Function EuroDateEn$(V)
    If IsDate(V) Then
                 D% = Day(V)
         EuroDateEn = D & Choose(IIf(D < 4, D, 4), "st", "nd", "rd", "th") & Format$(V, " mmm yyyy")
    End If
End Function

 

[Marc L]

According to post #1 attached workbook,
a way to convert dates to english string :​

Sub Demo()
Dim Rg As Range
Application.ScreenUpdating = False
                        AR = Split("th st nd rd")
With Sheet1.Cells(1).CurrentRegion.Rows
    With .Item("2:" & .Count).Columns(2)
         .NumberFormat = "[$-809]d mmm yyyy"
                .Value = .Offset(, -1).Value
        For Each Rg In .Cells
                    SP = Split(Rg.Text)
                 SP(0) = SP(0) & AR(IIf(SP(0) > 3, 0, SP(0)))
              Rg.Value = Join$(SP)
        Next
    End With
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

 

[NARAYANK991]

Hi Marc ,

My point was never the date format ; the point is that January 21, 2015 in what ever format you may enter it , has to be converted and displayed as :

21st January, 2015

where the key issue is how to use st as the suffix.

Narayan

 

[Marc L]

It seems I forgot the rule … (poor french lost in english !)​

Sub Demo1()
Dim Rg As Range
AR = Split(" st nd rd" & [REPT(" th",17)] & " st nd rd" & [REPT(" th",7)] & " st")
Application.ScreenUpdating = False

With Sheet1.Cells(1).CurrentRegion.Rows
    With .Item("2:" & .Count).Columns(2)
         .NumberFormat = "[$-809]d mmm yyyy"
                .Value = .Offset(, -1).Value
        For Each Rg In .Cells
                    SP = Split(Rg.Text)
                 SP(0) = SP(0) & AR(SP(0))
              Rg.Value = Join$(SP)
        Next
    End With
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

An efficient formula way :​

Sub Demo2()
Application.ScreenUpdating = False

With Sheet1.Cells(1).CurrentRegion.Rows
    With .Item("2:" & .Count).Columns(2)
         .Formula = "=DAY(A2)&MID(""thstndrdth"",MIN(9,2*RIGHT(DAY(A2))*(MOD(DAY(A2)-11,100)>2)+1),2)&TEXT(A2,""[$-809] " _
                    & String(3, Application.International(xlMonthCode)) & " " _
                    & String(4, Application.International(xlYearCode)) & """)"
         .Formula = .Value
    End With
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[ThrottleWorks]

Hi @Marc L , thanks a lot for the help and your valuable time. I am checking it and will revert in case of any issue.

Hi @NARAYANK991 Sir, thanks a lot for the help and your valuable time.

Have a nice day ahead.

 

[Marc L]

You're welcome !

Demo2 is my favorite but if you need to directly overwrite column A,
just have to amend Demo1 …
​

 

[Marc L]

See also this thread, in particular post #23 …


​

 

[ThrottleWorks]

Hi @Marc L , sure, thanks a lot for the help. I will definitely check this. Good night.