Last day of the month

[hegdepavithra10]

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. 

[hegdepavithra10]

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

[atcodedog05]

Hi @hegdepavithra10 

Can you give a expected output.

[hegdepavithra10]

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.

[hegdepavithra10 · Accepted solution]

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"))

[atcodedog05]

Hi @hegdepavithra10 

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 🙂

[Ladarthure]

Hi, to get the first of the month, you can use DateTimeTrim([date], 'firstofmonth') 🙂