Select last day of the month

[martavertone]

Hello,

I would need help with the following.

I have a column called "Creation Date" which displays date and time in this format 2018-07-18 11:57:10.

This date and time correspond to the creation date and time of several daily files in my folder.

I need to find a way to filter from this column only the last day of each month.

Would anyone be able to suggest how should I do?

thank you

Marta

[patrick_digan · Accepted solution]

@martavertone If you're running 2018.1 or above, you can use this in a filter tool:

DatetimeDay(DateTimeAdd(Left([Field1],10),1,"days"))=1

EDIT: if you don't have the DateTimeDay function available, then you can use the substring function:

Substring(DateTimeAdd(Left([Field1],10),1,"days"),8,2)="01"

[martavertone · Accepted solution]

Awesome! It works!

thank you sooo much!!!

Cheers

Marta

[martavertone]

Hi guys,

another help on this.

I was applying in the filter tool this expression to get from the column Creation Time the last day of each month only.

DatetimeDay(DateTimeAdd(Left([CreationTime],10),1,"days"))=1

The formula work, however I have notices that it returns only April and May but not June and July.

Any clue why this is happening?

thank you

Marta

[patrick_digan · Accepted solution]

Can you post the date the date that isn't working as expected? You mentioned that June isn't working, but "2018-06-30 11:00:00" works on my end.

Perhaps I misunderstood your original question. I was thinking you wanted to find those records that are the last day of each calendar month (ie Jan 31, Feb 28, March 31, Apr 30....). Instead are you maybe looking for the latest day of each month that is in your dataset? If that's the case, I've attached a v2018.1 workflow that uses a formula to get the month, sorts the data, and then uses a sample tool to get the latest date for each month.

[martavertone]

Yes correct apologies it was rather the latest day of each month. It works now, thank you!!

Cheers,

Marta

[hatounkhu_]

Hello Mr. Patrick,

I have used your solution to output the last day of each month and it have worked but there is an issue, it exactly doesn't output each month. as you see in the table there no dates such as 2017-03-31, 2017-06-31, 2017-09-31.. and so on. how to solve this?

I would highly appreciate your time and help. Thanks in advance. 

[joelmiller66]

@hatounkhu_  - Here is a solution attached.