Previous Month Date (1 Viewer)

MattioMatt · Jul 10, 2018

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 · Jul 10, 2018

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

Minty · Jul 10, 2018

As Gasman has said

Code:

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

Code:

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

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

pbaldy · Jul 10, 2018

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 · Jul 10, 2018

pbaldy said:
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 · Jul 13, 2018

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

CJ_London · Jul 13, 2018

the other way is

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

Previous Month Date (1 Viewer)

MattioMatt

Registered User.

Gasman

Enthusiastic Amateur

Minty

AWF VIP

pbaldy

Wino Moderator

Gasman

Enthusiastic Amateur

MattioMatt

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread