End of previous month

[Steve]

What function would I need to use to calculate the last
calendar day of last month.

If I run a macro on 2nd Feb I want my date to default to
31-Jan-04.

If I run it on 27th April I want it to default to 31-Mar-
04. Is this possible ?

Thanks in advance

 

[Tim Coddington]

In a spreadsheet cell, you can use =date. Like
'=DATE(YEAR(TODAY()),MONTH(TODAY()),0)'
In excel macros, see the dateserial function for similar types of work.
Also dateadd may help.

 

[Dan H.]

Sub test()
Dim EndofLastMonth As Date
EndofLastMonth = Date - Day(Date)
MsgBox EndofLastMonth

End Sub>-----Original Message-----

 

[kkknie]

datLastMonth = DateSerial(Year(Now), Month(Now), 1) - 1

Basically, the first day of the current month - 1...

 

[Tod]

Here is one way:

Dim LastDayOfLastMonth As Date
LastDayOfLastMonth = _
DateValue(Month(Date) & "/1/" & Year(Date)) - 1

tod

 

[Bob Phillips]

=date(Year(A1),month(A1),0)