Home Data Prep Q&A

Big News: we’ve moved to the DataRobot Community! Please keep your eye out for an email invitation to join us there. Refer to the We've Moved FAQ for a guide on how to use your existing Paxata Community account to login to our new home.

Visit the official Paxata Documentation portal for all of your doc needs.

DATEVALUE() - how to handle "23rd" and "21st" in the day of month position

I have a text column with a value like this: Tue, July 3rd 2018
I want to convert it to a Date column. My formula looks like this:

DATEVALUE(@[email protected] ,"E, MMM d'th' yyyy")

How do I make the formula handle not only 'th' but also 'nd', 'rd', and 'st'?

Best Answer


  • CSDanCSDan Posts: 31 ✭✭
    edited November 9, 2018 10:06PM
    Thanks - @ebarre - that's worked except for Thursdays (not all are uppercase) - easy enough to work around - probably not the best regex, but it got me past Thu.
    REGEXP(@[email protected] ,"(.*[0-9])(nd|st|th|rd)( .*)","$1$3")
Sign In or Register to comment.