Date Parsing issues

[SAPaul]

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.

[PaulRB]

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...

[CharlieS · Accepted solution]

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. 

[PaulRB · Accepted solution]

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.

[SAPaul]

Hi Charlie 

Thanks, this worked great, even when there were posts more than 2 days old!

[SAPaul]

Hi PaulRB

Thank you for this it, worked perfectly.