Dates

[Andrew]

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]

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

 

[keepITcool]

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]

Duane,

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

Andrew

Re: 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


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