13 Replies Latest reply: Mar 23, 2016 9:03 PM by jagan mohan rao appala

Last Date Of the Month

Hi All

How to get the last date of the month in the expression Max({<Year={\$(=Max(Year))},Month={\$(=Month(Max(TranDate)))}>} TranDate)

where TranDate is in DD/MM/YYYY format and I need the last date of the month also in the same format.

• Re: Last Date Of the Month

May be this:

Date(Max({<Year={\$(=Max(Year))},Month={\$(=Month(Max(TranDate)))}>} TranDate), 'DD/MM/YYYY')

• Re: Last Date Of the Month

Although I wonder if this is working also:

Date(Max({<TransDate>}TransDate), 'DD/MM/YYYY')

• Re: Last Date Of the Month

Date(MonthStart(max(TranDate),1)-1,'DD/MM/YYYY')

• Re: Last Date Of the Month

Gysbert is there a reason you are first calculating the next month's start and then subtracting 1 to get month end? Is there a reason not to use Date(MonthEnd(Max(TranDate)),'DD/MM/YYYY')

• Re: Last Date Of the Month

Monthend returns a timestamp, not a date.

• Re: Last Date Of the Month

Isn't that true for MonthStart as well? I though it returned a TimeStamp as well. I might be totally wrong and it would be a good to know kind of thing

• Re: Last Date Of the Month

Yeah, but the time fraction of a date returned by monthstart is 0. The time fraction of a date returned by monthend however is 1 millisecond less than a full day. It makes a difference when you want to compare or select the last date in a month with the result from a calculation that should return the last date of a month. MonthEnd won't unless you use the floor function on its result. MonthStart(Date, 1)-1 will give you the last day of the month too.

• Re: Last Date Of the Month

Got it

Thanks for the explanation

• Re: Last Date Of the Month

chk dis

• Re: Last Date Of the Month

Hi,

Try like this

Date(Max({<Year={'\$(=Max(Year))'},Month={'\$(=Month(Max(TranDate)))'}, TranDate=>} TranDate), 'DD/MM/YYYY')

Hope this helpsyou.

Regards,

jagan.

• Re: Last Date Of the Month

Its always giving date in no. like 42429 for 29/02/2016.

Best Regards

PKP

• Re: Last Date Of the Month

Use Date() on top this, it will convert to Date format.

Regards,

Jagan.