Hi All,
I have a field with all the dates. The end of this data will be of current month. And this has historical dates.
The requirement is to extract all the days from current month -1 and last day of current month-2.
I have used the following formula in the filter tool, but the output shows as null.
Solved! Go to Solution.
This is the formula I have used
(DateTimeFormat([Date1],"%b") = DateTimeFormat(DatetimeAdd(DateTimeToday(),-1,"month"),"%b") AND DateTimeFormat([Date1],"%y") = DateTimeFormat(DateTimeToday(),"%y")) AND (DateTimeformat(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"),"%d")=DateTimeFormat([Date1],"%d") AND DateTimeformat(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"),"%b")=DateTimeFormat([Date1],"%b") AND DateTimeformat(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"),"%y")=DateTimeFormat([Date1],"%y"))
@atcodedog05 could you please help
Hi @atcodedog05
So i consider this month to be current month then i need all the dates from november and 31st of october to be extracted.
However, i have solved this issue. instead of AND in between the two groups of formula OR must be used.
if anybody is interested in the solution
(DateTimeFormat([Date1],"%b") = DateTimeFormat(DatetimeAdd(DateTimeToday(),-1,"month"),"%b") AND DateTimeFormat([Date1],"%y") = DateTimeFormat(DateTimeToday(),"%y")) OR (DateTimeformat(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"),"%d")=DateTimeFormat([Date1],"%d") AND DateTimeformat(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"),"%b")=DateTimeFormat([Date1],"%b") AND DateTimeformat(DateTimeAdd(DateTimeLastOfMonth(),-2,"month"),"%y")=DateTimeFormat([Date1],"%y"))
Nice you see you have solved it. Here is my take on it
DateTimeAdd(DateTimeFormat(DateTimeAdd(DateTimeFormat(DateTimeNow(),"%Y-%m-01"),-1,"days"),"%Y-%m-01"),-1,"days")<=[Date]
and
[Date]<=DateTimeAdd(DateTimeFormat(DateTimeNow(),"%Y-%m-01"),-1,"days")
Workflow:
Hope this helps 🙂
Hi, to get the first of the month, you can use DateTimeTrim([date], 'firstofmonth') 🙂