Determine Last working day of Month

Is there any way to determine the last working day of a month by using code

Thanks

=DATE(YEAR(A6),MONTH(A6)+1,0)-(WEEKDAY(DATE(YEAR(A6),MONTH(A6)+1,0),2)>5)-(W
EEKDAY(DATE(YEAR(A6),MONTH(A6)+1,0),2)=7)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Nigel" <[email protected]> wrote in message
news:[email protected]...
> Is there any way to determine the last working day of a month by using
code
>
> Thanks

Hi Nigel,

supposing that Input_Date is a date:

(Int(CDbl(Input_Date)) - 1) Mod 7 = 6 'saturday
(Int(CDbl(Input_Date)) - 1) Mod 7 = 0 'sunday

Did it help?

Regards,
Ivan

Dim dt as Date
Dim dtLastDay as Date
dt = date
dtLastDay = DateSerial(year(dt),month(dt)+1,0)
if Weekday(dtLastDay,vbMonday) > 5 then
dtlast = dtLastDay - (Weekday(dtLastDay,vbMonday) - 5)
' consider holidays?
end if

--
Regards,
Tom Ogilvy

"Nigel" wrote:

> Is there any way to determine the last working day of a month by using code
>
> Thanks

Probably pre-empting the original question, but the request was for
last working day of a month - the code that has been given gives the
last weekday of the month (ie Monday through Friday) - what about if
that day is a public or other holiday - that would need to be
incorporated which would make the project a little more complicated! I
have done it in Access, with a table for holidays, and there are
functions in excel we could use, but not sure about "IN CODE"

I'm not sure precisely how to work this in - but you can use the
=workday(a,b,c) function in the analysis tookpak to help with this (there is
also a vba version)

a = start date
b = number of working days to count
c = range with a list of holidays (or the list itself I guess).
this will avoid weekends as well as the list of holidays.
haven't thought it out on how to use this - this function is also available
to a VBA routine - however I'm not sure how to get one add-in to call a
routien in another add-in - I have a question pending on that myself - hope
this helps


"Nigel" wrote:

> Is there any way to determine the last working day of a month by using code
>
> Thanks

Originally Posted by Tom Ogilvy

Dim dt as Date
Dim dtLastDay as Date
dt = date
dtLastDay = DateSerial(year(dt),month(dt)+1,0)
if Weekday(dtLastDay,vbMonday) > 5 then
dtlast = dtLastDay - (Weekday(dtLastDay,vbMonday) - 5)
' consider holidays?
end if

--
Regards,
Tom Ogilvy

"Nigel" wrote:

> Is there any way to determine the last working day of a month by using code
>
> Thanks

This does not work for the dt= "12/01/1970" (and, perhaps other years).

Is there something that I am missing?

Thanks in advance for any help.

Hokie