Ladies & Gents,

I was wondering if someone can help me with some date problems with an Excel
macro. I'm using a macro to send an email, and in that email I need 3
dates, the last working day of the month, the current month, and the first
working day of the new month. The email will be sent before the last
working day of the month. I can include the current month in the email, as
indicated below, but I am having problems with the other 2 dates. Can
someone please give me some help with working days in the month?

emlMthDate = Date - 27 ' To allow for February

Format(emlMthDate, "mmm-yyyy")


TIA

Andrew

this spreadsheet figures your days out - i'm sure it could be coded in
vba. John

Attachment filename: working days.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=614843
---
Message posted from http://www.ExcelForum.com/

Try like:
Sub DateStuff()
Dim dBegMonth As Date
Dim dEndMonth As Date
Dim d1stMonday As Date

dBegMonth = Date - Day(Date)

dEndMonth = DateSerial(Year(Date), Month(Date) + 1, 0)

d1stMonday = dEndMonth - Weekday(dEndMonth, 2) + 8

MsgBox Format(dBegMonth, " dddd, d mmm yyyy") & vbNewLine & _
Format(dEndMonth, " dddd, d mmm yyyy") & vbNewLine & _
Format(d1stMonday, " dddd, d mmm yyyy")
End Sub


i've kept it simple.. to 1st monday.. ignoring holidays :(



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Andrew" wrote:

Ladies & Gents,

I was wondering if someone can help me with some date problems with an
Excel macro. I'm using a macro to send an email, and in that email I
need 3 dates, the last working day of the month, the current month,
and the first working day of the new month. The email will be sent
before the last working day of the month. I can include the current
month in the email, as indicated below, but I am having problems with
the other 2 dates. Can someone please give me some help with working
days in the month?

emlMthDate = Date - 27 ' To allow for February

Format(emlMthDate, "mmm-yyyy")


TIA

Andrew

Duane,

Er.... um.... I can't seem to find any formulas on that sheet.... :-)

Andrew

Dates
From: duane
Date Posted: 7/11/2004 9:17:00 PM



this spreadsheet figures your days out - i'm sure it could be coded in
vba. John

Attachment filename: working days.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=614843
---
Message posted from http://www.ExcelForum.com/


Andrew wrote in message ...
Ladies & Gents,

I was wondering if someone can help me with some date problems with an Excel
macro. I'm using a macro to send an email, and in that email I need 3
dates, the last working day of the month, the current month, and the first
working day of the new month. The email will be sent before the last
working day of the month. I can include the current month in the email, as
indicated below, but I am having problems with the other 2 dates. Can
someone please give me some help with working days in the month?

emlMthDate = Date - 27 ' To allow for February

Format(emlMthDate, "mmm-yyyy")


TIA

Andrew