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

Highlighted
Linear Actuator
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(@Date@ ,"E, MMM d'th' yyyy") How do I make the formula handle not only 'th' but also 'nd', 'rd', and 'st'?
Labels (1)
0 Kudos
2 Replies
Highlighted
NiCd Battery
One way of achieving this is to re-writte the formula to include a regular expression that strips out those terms, simplifying the date representation and then applying the DATEVALUE function itself: DATEVALUE(regexp(@date@,"(st|rd|th|nd)",""),"E, MMM d yyyy") ng8d1hhk9l8s.png
0 Kudos
Highlighted
Linear Actuator
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(@Date@ ,"(.*[0-9])(nd|st|th|rd)( .*)","$1$3")
0 Kudos