How I can write a macors for the given steps.

[pol]

Please execuse me for resposting the same question again. I afraid the
previous question will give correct result.

I want to write a macros for the following program steps. from there I can
find out the number of offdays of each employee

Date l_dat_tart = ’01-Jan-2009’
Date l_dat_to = ’31-12-2009’
Int li_sat


DO WHILsE l_dat_tart <= l_dat_to
ls_day = dayname(l_dat_start)

Choose Case ls_day
Case ‘Saturday’
li_sat = li_sat+ 1

if Range("Q3").Value = 1 and li_sat= 1 then
li_sat= li_sat+ 1
end if

if Range("Q3").Value = 2 and li_sat= 2 then
li_sat= li_sat+ 1
end if

if Range("Q3").Value = 3 and li_sat= 3 then
li_sat= li_sat+ 1
end if

l_dat_start= l_dat_start + 1
LOOP

With thanks
Pol

 

[AltaEgo]

Your message indicates you are only interested in counting Saturdays. If so,
this may help:
http://www.mrexcel.com/archive/Formulas/6835a.html

If not, it sounds like you are just trying to create the compliment of the
Networkdays() function to work out the number of weekend days off. If this
is the case, why not find total days between dates and subtract
Networkdays?
=A2-A1-NETWORKDAYS(A1,A2)

http://office.microsoft.com/en-us/excel/HP052091901033.aspx

NOTE that Networkdays may require you install Analysis pack add in.

If you must perform the calculation in VBA, you can call worksheet functions
from VBA. As NetworkDays() is in the Analysis Toolpack, you may need to read
"Calling Analysis Tool Pack Function In VBA" on the site below for guidance
on calling this from VBA.

http://www.cpearson.com/excel/CallingWorksheetFunctionsInVBA.aspx

Once the Analysis pack is installed as above:


Function DaysOff(StDate As Date, EndDate As Date)

Dim NWDdays, TotDays

NWDdays = networkdays(StDate, EndDate)
TotDays = EndDate - StDate

DaysOff = TotDays - NWDdays

End Function

 

[AltaEgo]

Finally found your earlier question and understand you are NOT trying to do
anything related to Networkdays but are looking for a way to count rostered
days off using a Rota.

Using wkDays code from the mrexcel site listed below (untested):

=roundup(WkDays(yourStartDate,yourEndDate,yourWeekday),0)

 

[AltaEgo]

My slips are REALLY showing today!

=roundup(WkDays(yourStartDate,yourEndDate,yourWeekday)/x,0)

Where x is your divisor relating to one in x off per month.