Im trying to identify items with end of month dates. In this scenario, i want to filter only items on 30th of June and 31st of July.
There is 1 line item thats falls under the 31st of July however i tried using the formula shown below but it didn't work.
I am able to get items from before June but not after.
Any advise?
Solved! Go to Solution.
Hi @JinOnn ,
Try this, which tests if a date is the last of the month by checking if date + 1 day has a different month:
DateTimeMonth(DateTimeAdd([DateTime_Out],1,"days"))
!=
DateTimeMonth([DateTime_Out])
Hi @JinOnn ,
in the second part of your formula you are getting : 2021-07-30 ( 2021-06-30 + 1 day).
Please try below:
[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),0,"month")) OR
[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))
regards,
Karolina
Hi again,
Actually below formula is enough for each month last day:
Karolina
[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))
Still not able to get what I want.
Im using the filter function to get the last day of the month for June and July.
However, the formula (as attached) was not able filter data in July.
However, when i use -1 or -2 as my interval, it works! I am just not able to use 1 to capture July's date
Hey Karolina!
I think you're close , however in this scenario I only want the dates for the month of June and July.
Is there another syntax I can try?
Hi @JinOnn ,
Have you tried the solutions posted by @KarolinaRoza or I?
DateTimeMonth(DateTimeAdd([DateTime_Out],1,"days"))
!=
DateTimeMonth([DateTime_Out])
[DateTime_Out]=todate(DateTimeTrim([DateTime_Out],'lastofmonth'))
If these don't give you the answer you want, can you clarify what you are trying to achieve?
Karolina is close! However, I want to only get the end of month date for June and July. Karolina's formula allows me to get all the end of month date from previous months.
What I am looking for is as shown above. The date time add formula works for -1 or -2 as my interval but it doesn't work for future months.
Hi @JinOnn
You can combine what @KarolinaRoza suggested with the DateTimeMonth function to limit the months to June and July only.
E.g.
[DateTime_Out]= DateTimeTrim([DateTime_Out],'LastOfMonth')
AND
(DateTimeMonth([DateTime_Out]) = 6 OR DateTimeMonth([DateTime_Out]) = 7)
Hi @JinOnn ,
I am not sure if your formula works correctly for previous months, if I apply:
[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),-1,"month")) -> receiving: 2021-05-30 - not correct, as last May day is: 2021-05-31
[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),-2,"month")) -> receiving: 2021-04-30 - OK
Please try:
[DateTime_Out]=todate(DateTimeTrim(DATETIMEADD(DateTimeToday(), 1, "month"),'lastofmonth')) OR
[DateTime_Out]=todate(DateTimeAdd(DateTimeLastOfMonth(),0,"month"))
it should work.
Karolina