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


"thomas donino" wrote:

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

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

"Sam Wilson" wrote:

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" wrote:

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

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" wrote:

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

"Sam Wilson" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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

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

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


"thomas donino" wrote:

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" wrote:

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" wrote:

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

thank you

"Sam Wilson" wrote:

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

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


"thomas donino" wrote:

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" wrote:

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" wrote:

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

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

--
Rick (MVP - Excel)


"thomas donino" wrote in message
...
thank you

"Sam Wilson" wrote:

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

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


"thomas donino" wrote:

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" wrote:

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" wrote:

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

On Wed, 12 Aug 2009 11:09:39 -0400, "Rick Rothstein"
wrote:

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

--
Rick (MVP - Excel)


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

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
===================

Yep, that is definitely a better formula to use.

--
Rick (MVP - Excel)