Previous Month Date

[MattioMatt]

Hello,

I'm trying to get the last date of the previous month based on a current month.

So for example if the current end month is 30/06/2018, I'd like it to return 31/05/2018.

I have a text box called txtCurrentDate (Value: 30/06/2018) I then have a second text box called txtPreviousDate with the following
=DateAdd("m",-1,[txtCurrentDate])

However this is pulling back 30/05/2018 but as I want it to be the last day of the previous month I'd like it to pull 31/05/2018. Similiary this issue happens for different dates.

Any ideas much appreciated

 

[Gasman]

The key is get the first of the current month and subtract 1 day.

 

[Minty]

As Gasman has said

DateSerial(Year(Date()), Month(Date()), 0)

Will do what you want. To experiment open the VBA editor and presgg Ctrl +G to bring up the immediate window type
? DateSerial(Year(Date()), Month(Date()), 0)
and hit enter

 ? DateSerial(Year(Date()), Month(Date()), 0) 
30/06/2018

The ? means print the result. Handy for fiddling with these type of expressions.

 

[pbaldy]

A trick is using 0 for the day so you don't have to subtract:

?dateserial(year(date),month(date),0)
6/30/2018

 

[Gasman]

A trick is using 0 for the day so you don't have to subtract:

?dateserial(year(date),month(date),0)
6/30/2018

I should have remembered that, as I have only recently posted it in another thread.:banghead:

 

[MattioMatt]

Thanks all this is perfect and exactly what I'm looking for

 

[CJ_London]

the other way is

=DateAdd("d",-Day([txtCurrentDate]),[txtCurrentDate])