last day of month in prior month

T

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
 
S

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
 
T

thomas donino

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

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.
 
T

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
 
S

Sam Wilson

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

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

thomas donino

thank you

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

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

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
 
R

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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top