last day of month in prior month

  • Thread starter Thread starter thomas donino
  • Start date Start date
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
 
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
 
=date does not work in VBA
tempdate =DATE(YEAR(A1),MONTH(A1),1)-1
produces a compilation error
 
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.
 
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
 
Change thsi:
tempdate = CDate(CStr(targMo) & ", " & CStr(0) & ", " & CStr(targYr))

to this
tempdate = CDate(CStr(targMo) & "/01/" & CStr(targYr))-1
 
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
Click to expand...
 
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
 
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
Click to expand...


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
 
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
===================
Click to expand...

Yep, that is definitely a better formula to use.
 
Back
Top