Determine Last working day of Month
 

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

Thanks

Determine Last working day of Month
 

=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" wrote in message
...
Is there any way to determine the last working day of a month by using
code

Thanks

Determine Last working day of Month
 

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

Determine Last working day of Month
 

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

Determine Last working day of Month
 

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"

Determine Last working day of Month
 

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