DATE FUNCTION (LAST DAY OF MONTH)

DATE FUNCTION (LAST DAY OF MONTH)

  

Hi, I would like to convert CurrDate() changing the number of the day with the last number of the month. For instance: if CurrDate() = #2017-09-14# result = #2017-09-30#.

Can you hel me?

Thanks

Roberto

Hi Roberto,

Try this:

AddDays(AddMonths(CurrDate(),   1), -Day(CurrDate()))

EDIT: I edited the answer since it was not right...

Cheers,

José

Solution

Hi Roberto,

Are you able to solve the problem? 

Check Date functions available AddDays(), AddMonths().. and there are others.

Solution

José Costa wrote:

Hi Roberto,

Try this:

AddDays(AddMonths(CurrDate(),   1), -Day(CurrDate()))

EDIT: I edited the answer since it was not right...

Cheers,

José


Hi Josè,

thank you very much. I have resolved : 

AddDays(NewDate(Year(CurrDate()),Month(CurrDate())+1,1),-1)

Many thanks

Roberto

Suraj Borade wrote:

Hi Roberto,

Are you able to solve the problem? 

Check Date functions available AddDays(), AddMonths().. and there are others.


Hi Suraj,

thank you very much. I have resolved : 

AddDays(NewDate(Year(CurrDate()),Month(CurrDate())+1,1),-1)

Many thanks

Roberto

Hello Roberto. I think your solution won't work in December, because you will be trying to construct a date with month 13.

Did you try the solution given by Jose Costa? I don't see anything wrong with it.

Hi Leonardo,

Actually, my solution was not right also: It did not take into account when the next month had less days than current one.

Thus, here it goes a new solution:

AddDays(NewDate(If(Month(Date) = 12,Year(Date)+1,Year(Date)),If(Month(Date) = 12, 1, Month(Date)+1),1),-1)

Cheers,

José

Can I offer a simpler solution?

AddDays( AddMonths( NewDate( Year(Date), Month(Date), 1 ), 1 ), -1 )

Cheers,
Eduardo Jauch

EDIT

If the objective is to do a comparison, you can simplify, doing this:

AddMonths( NewDate( Year(Date), Month(Date), 1 ), 1 )

And than, to compare, use the <

DateToCompare < LastDate

Hi Eduardo,

Very nice solution. Much more elegant than mine. :)

Cheers,

José