UPDATED: Still trying to figure this out, got pretty close and then hit a scenario I missed...
Event_Date | Orig_Event_Date |
OCTOBER 29 | OCTOBER 29, NOVEMBER 5 & 12 |
NOVEMBER 5 | OCTOBER 29, NOVEMBER 5 & 12 |
OCTOBER 12 | OCTOBER 29, NOVEMBER 5 & 12 |
OCTOBER 31 | OCTOBER 31, NOVEMBER 7, 14 & 21 |
NOVEMBER 7 | OCTOBER 31, NOVEMBER 7, 14 & 21 |
OCTOBER 14 | OCTOBER 31, NOVEMBER 7, 14 & 21 |
OCTOBER 21 | OCTOBER 31, NOVEMBER 7, 14 & 21 |
I missed another scenario to make it more fun.... I know the answer is Regex probably or some combination of formulas and regex, but I'm in a time crunch and trying to resolve this quickly. Hopefully I figure it out before anyone else does and if so I'll post the result.
I have a column where they decided to roll up event dates where the day of the week and the time of the event were the same just the date of the event changed, I need to unroll this data to append attendance information by date and time to the meeting. Time is in a separate column so that's not an issue. The way the formatted the rolled up dates is slight inconsistent which is what has me scratching my head a little. So if the event fell in the same month they just added an & and the number, I need to add the Month to that so when I parse it to new rows I know what month it was in. So far easy enough to parse on the & with a text to columns oh and then I guess I could use a formula tool to look back at the Event_Date field and where the new Date field has just the numeric value I can insert the Month there. The wished for end result is an actual date formatted field.
INPUT Desired Output
Event_Date Date
NOVEMBER 19, 26 & DECEMBER 3 2018-11-19
2018-11-26
2018-12-03
DECEMBER 1 2018-12-01
NOVEMBER 27 & DECEMBER 4 2018-11-27
2018-12-04
NOVEMBER 29 2018-11-29
OCTOBER 18 2018-10-18
OCTOBER 18 & NOVEMBER 1 2018-10-18
2018-11-01
OCTOBER 20 2018-10-20
OCTOBER 23 & 30 2018-10-23
2018-10-30
Solved! Go to Solution.
Hi @NJT !
I'm not so sharp at RegEx yet, but here's one way to muscle it into a DateTime format. I think I threw every tool in the kit at this one, so I'd love to see a better solution!
I should have included an eventID on that sample data, your flow definitely works, now I just have to figure out how to join these dates back to the original data in the larger dataset I'm working on. This is by far the most annoying data set I've had in a while, especially considering when I gave it to them I had cleaned up the event list and added eventIDs to I could match it up later with attendance records and count of prospects mailed to. And in the mail file they sent me back they had rolled up events so dropped the event IDs, and in the attendance file they dropped everything and put all the event location information into one field that I had to parse out and clean up again to get the locations to match the events on the mail file. And with up to 4 events per prospect and up to 4 dates per event at the same time day of the week and location... unraveling all this just to figure out how many prospects were mailed for each event location and how many people attended each event has been a challenge.
@NJT Ah, the struggle is real when dealing with people that collect data... but don't understand how data might be analyzed. I feel your pain.
If you have a sample dataset with the eventID, I'd be happy to take a stab at it.