I am trying to create a dynamic workflow from some webscraped data and have become stuck when it comes to date parsing numbers like in the picture below. essentially if its from the last 3 days, it comes in the format of 'today', 'yesterday' and as below '2 nov' and '1 nov', i have come up with a solve for yesterday and today as those phrases will not change, however, i have not been able to figure out a way to get the correct year on these examples. Any help would be appreciated.
Solved! Go to Solution.
You can use the formula tool with this formula:
DateTimeAdd(DateTimeParse([REVMONTH],"%d %b"), DateTimeYear(DateTimeToday()) - 1400, "years")
This won't work correctly in early Jan when the dates refer to Dec from the previous year...
It's going to be within the last few days, here's a formula you could use:
IF Trimleft(DateTimeFormat(DateTimeAdd(DateTimeToday(),-2,"days"),"%d %b"),"0")=[REVMONTH] THEN
DateTimeParse([REVMONTH]+DateTimeFormat(DateTimeAdd(DateTimeToday(),-2,"days"),"%Y"),"%d %b %Y")
ELSE
DateTimeParse([REVMONTH]+DateTimeFormat(DateTimeAdd(DateTimeToday(),-3,"days"),"%Y"),"%d %b %Y")
ENDIF
Let's break this down:
- The overall IF statement is used to determine if the value is 2 or 3 days from the current date so that the appropriate year can be used in the subsequent condition formula.
- Since the date values start appearing on dates that are 2 days old, DateTimeAdd(DateTimeToday(),-2,"days") take the date from two days ago and determines the year
- Once the year is determined and returned by using the "%Y" format, that string is added to the original REVMONTH input. This makes "2 Nov" into "2 Nov 2019".
- Now that the year is appended, the standard DateTime value is returned. This is ISO 8601 format, which is "2019-11-02"
I'm sure you'll find many other ways to go about this, but this should help get things started.
Try the attached. Should deal with the Jan vs Dec problem I mentioned!
PS. Are you in Leeds area? There is a Leeds Alteryx User Group on the forum. Join that to find out date/location of next meeting.
Hi Charlie
Thanks, this worked great, even when there were posts more than 2 days old!
Hi PaulRB
Thank you for this it, worked perfectly.