Challenge #4: Date Parsing
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
margiecaina
6 - Meteoroid
12-06-2024
05:48 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
snothando_hlongwane
8 - Asteroid
12-10-2024
02:09 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thomasduong
8 - Asteroid
12-17-2024
03:19 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here is my solution to Challenge #4
Spainey
9 - Comet
12-19-2024
01:40 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
A nice regex solution to this date problem.
Spoiler
I have used the Regex_Match and Regex_Replace functions to identify specific patterns in the data and then pull out only the data I needed to a raw date column. After this, I had 2 different formats of dates, so did a different DateTimeParse operation on each pattern.
Date_raw =
IF Contains([Field_1], "-")
THEN UpperCase(REGEX_Replace([Field_1], ".*?(\d+-[A-Za-z]+-\d+).*", "$1"))
ELSEIF REGEX_Match([Field_1], ".*?[A-Za-z]{3} \d+,? \d{4}.*")
THEN REGEX_Replace([Field_1], ".*?([A-Za-z]{3}) (\d+),? (\d{4}).*", "$2-$1-$3")
ELSE ""
ENDIF
Date
IF REGEX_Match([Date_raw], "\d+-[A-Z]+-\d{4}")
THEN DateTimeParse([Date_raw], "%d-%b-%Y")
ELSEIF REGEX_Match([Date_raw], "\d+-[A-Z]+-\d{2}")
THEN DateTimeParse([Date_raw], "%d-%b-%y")
ELSE ""
ENDIF
cmoussa
8 - Asteroid
12-30-2024
05:48 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
martinson
11 - Bolide
01-07-2025
05:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
nICE
Darby_Lehane
6 - Meteoroid
01-20-2025
05:45 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Dominic_Brady
8 - Asteroid
02-03-2025
05:02 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Jfoss
7 - Meteor
02-03-2025
12:21 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
holly_jones12
7 - Meteor
02-07-2025
08:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
A tricky one to get my head round with the RegEx