topic Cannot format date to e.g. 1st Mar 2025 in Product Forum

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

rabbanir — Tue, 04 Mar 2025 12:58:13 GMT

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,

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

SayeedBinAbdullah — Tue, 04 Mar 2025 16:56:28 GMT

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

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

Mohamad_Dakkouri — Tue, 04 Mar 2025 17:38:41 GMT

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

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

rabbanir — Wed, 05 Mar 2025 13:43:19 GMT

Hi sayeed,

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

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

SayeedBinAbdullah — Wed, 05 Mar 2025 14:07:41 GMT

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_Dakkouri 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

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

SayeedBinAbdullah — Thu, 06 Mar 2025 05:04:47 GMT

Hi @rabbanir,

Please find the screenshots attached,

Best Regards,
Sayeed Bin Abdullah