last day of month in prior month

[thomas donino]

I am having trouble writing a function that returns the last day of the month
prior to the month from the date in the cell.

I am parsing the date apart into 3 variables, year,date,month
subtracting 1 from month
where i am having trouble is how to put it back together

thank you

 

[Sam Wilson]

Parse into year, month & the number 1 then take a day off - otherwise a 30
day month after a 31 day month will cause you problems etc...

=DATE(YEAR(A1),MONTH(A1),1)-1

 

[thomas donino]

=date does not work in VBA
tempdate =DATE(YEAR(A1),MONTH(A1),1)-1
produces a compilation error

 

[Sam Wilson]

Ah, I see. That was a function based method. This works for me:

Dim xdate As Date
xdate = Now()
MsgBox CDate("01/" & Month(xdate) & "/" & Year(xdate)) - 1

Swap month & date if you're American.

 

[thomas donino]

Here is the current code which is not working

Public Function LastDayInLastMo(TargDate As Date) As Date
'get the last day of the month prior to the date in the cell
Dim targMo As Integer
Dim targYr As Integer
Dim tempdate As Date

targMo = Month(TargDate) - 1
targYr = Year(TargDate)
tempdate = DateValue(TargDate)
tempdate = CDate(CStr(targMo) & ", " & CStr(0) & ", " & CStr(targYr))
LastDayInLastMo = tempdate
End Function

 

[Sam Wilson]

Change thsi:
tempdate = CDate(CStr(targMo) & ", " & CStr(0) & ", " & CStr(targYr))

to this
tempdate = CDate(CStr(targMo) & "/01/" & CStr(targYr))-1

 

[thomas donino]

thank you

Change thsi:
tempdate = CDate(CStr(targMo) & ", " & CStr(0) & ", " & CStr(targYr))

to this
tempdate = CDate(CStr(targMo) & "/01/" & CStr(targYr))-1

 

[Rick Rothstein]

Here is a simpler function for you to consider...

Public Function LastDayInLastMo(TargDate As Date) As Date
'get the last day of the month prior to the date in the cell
LastDayInLastMo = DateSerial(Year(TargDate), Month(TargDate), 0)
End Function

 

[Ron Rosenfeld]

Here is a simpler function for you to consider...

Public Function LastDayInLastMo(TargDate As Date) As Date
'get the last day of the month prior to the date in the cell
LastDayInLastMo = DateSerial(Year(TargDate), Month(TargDate), 0)
End Function

Or, perhaps even simpler, using only one function and one arithmetic operation.

==================
Public Function LastDayInLastMo(TargDate As Date) As Date
'get the last day of the month prior to the date in the cell
LastDayInLastMo = TargDate - Day(TargDate)
End Function
===================
--ron

 

[Rick Rothstein]

Here is a simpler function for you to consider...

Or, perhaps even simpler, using only one function and one arithmetic
operation.

==================
Public Function LastDayInLastMo(TargDate As Date) As Date
'get the last day of the month prior to the date in the cell
LastDayInLastMo = TargDate - Day(TargDate)
End Function
===================

Yep, that is definitely a better formula to use.