how to get the last day of the month

[fabs]

Hello, currently I have the below formula. It is formulated in a way that anyone who ran the tool in the specific month, the last day of the current month should show. However, I am just wondering how should I update my formula to show October 31, 2022 as the last day instead of just October 30, 2022. 

DateTimeFormat(DateTimeAdd(DateTimeLastOfMonth(),-1,"months"),'%Y-%m-%d')

Thanks for your help in advance!!!

[IraWatt]

Hey @fabs,

I think you want the datetimetrim function it is the easiest way to get the last day of the month:

DateTimeTrim(DateTimeNow(),"lastofmonth")

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[fabs]

Hi @IraWatt, sorry I meant to say, it was formulated in a way that whoever run the tool, the last day of the month from the day s/he run the tool should show.

So for example, today is 11/24/2022, if I run the tool today, it should return a value date of 10/31/2022.

[ShankerV]

Hi @fabs 

Please find the output you are expecting, 

I know we can simplify within one formula, but to explain deriving the result, I need to split down as below.

Formula:

datetimetrim(datetimenow(), "lastofmonth")

DATEADD([Result],-1,"month")

DATEADD([Result],+1,"day")

Kindly accept this solution if it provided a solution to your question.

Many thanks

Shanker V

[IraWatt]

Ah okay @fabs you can just minus 1 month of the current date then use dattimetrim:

DateTimeTrim(DateTimeAdd(DateTimeNow(),-1,"months"),"lastofmonth")

[fabs]

Hi @ShankerV, if my understanding is correct then, that formula will only be applicable to month dates which should end with "31".

There are still months which should retain to "30".

[ShankerV]

Hi @fabs 

The below solution will work for all the months, kindly check from your end too!!!

datetimetrim(dateadd(datetimenow(),-1,"months"),"lastofmonth")

Many thanks

Shanker V

[ShankerV]

Hi @fabs 

DateTimeFormat(DateTimeAdd(DateTimeLastOfMonth(),-1,"months"),'%Y-%m-%d')

Trying to help you on what went wrong in your formula you tried.

1. You took the DateTimeLastOfMonth()
So It takes current month last date which is 30/Nov/2022 - 1month leads to 30/Oct/2022

Hence you were not able to reach the result.

Many thanks

Shanker V