Cannot format date to e.g. 1st Mar 2025

[rabbanir]

Hi,

I want to format a date from 01 Mar 2025 to 1st Mar 2025, 21 Mar 2025 to 21st Mar 2025 etc.

I've tried looking at formatdate but cannot find the correct pattern, in fact I don't think blue prism recognises 1st Mar 2025 as a valid date so is it even possible with the formatdate function? Any help on this would be appreciated,

[SayeedBinAbdullah]

Hi @rabbanir 

To get the ordinal suffix(st,nd,th,rd) you might have to create a custom VB code and then concatenate it using expression in the calculation stage.

Do let me know if this works for you, will share the detailed steps.

Best Regards,

Sayeed Bin Abdullah 

[Mohamad_747]

Hi @rabbanir 

If you want to transform 1st Mar 2025 to 01 Mar 2025 you need a custom code or a string manipulation with Instr and Mid.

But if you need to transform 01 Mar 2025 to 1st Mar 2025 you can use this :

FormatDate("01 Mar 2025","dd")&"st "&FormatDate("01 Mar 2025","MMM")&" "&FormatDate("01 Mar 2025","yyyy")

your output will be a TEXT

[rabbanir]

Hi sayeed,

Thanks for the reply. Yes that sounds like the way forward, if you could provide that would be great, thanks

[SayeedBinAbdullah]

Hi Rabbani,

To get the ordinal suffix below VB code can be used,

Dim day As Integer
Dim suffix As String
Dim month As String
Dim year As String
Dim formattedDate As String

day = DatePart("d", [YourDateField])
month = Format([YourDateField], "MMM")
year = DatePart("yyyy", [YourDateField])

Select Case day
        Case 1, 21, 31
               suffix = "st"
        Case 2, 22
               suffix = "nd"
        Case 3, 23
               suffix = "rd"
        Case Else
               suffix = "th"
    End Select

formattedDate = CStr(day) & suffix & " " & month & " " & year

Input Parameter - YourDateField (Date)
Output Parameter - formattedDate (Text)

Once we have the suffix as @Mohamad_747 we can append the suffix to the date using below expression,

"Day([CurrentDate]) & [formattedDate] & " " & Left(MonthName(Month([CurrentDate])), 3) & " " & Year([CurrentDate])"

Best Regards,

Sayeed Bin Abdullah

[SayeedBinAbdullah]

Hi @rabbanir,

Please find the screenshots attached,

Best Regards,
Sayeed Bin Abdullah