Hi,
Below is a function which counts working days(i.e. don't count sunday and saturday) between specified dates.
--------------------------------------------------
Function GetWorkingDay(dtFrom1,dtTo1)
            dim cd
            dim iFD
            dim cntr1
            dim i
            dim d
            dim wday
            dim objRSHL
            set objRSHL = Server.CreateObject("ADODB.Recordset")
            'IFD STORES NUMBER OF DAY IN CURRENT MONTH. SAY 30 FOR JUNE
            'iFD = datepart("d",dateserial(year(cd),month(cd)+1,1-1))
            iFD = DateDiff("d",dtFrom1,dtTo1) + 1

            cntr1 = 0

            for i = 0 to iFD
                    'd = datepart("w",dateadd("d",i,dateserial(year(cd),mon th(cd),1)))
                    d = datepart("w",dateadd("d",i,CDate(dtFrom1)))
                    'IF IT IS SUNDAY OR SATURDAY INCREMENT COUNTER
                    if (d = 1 or d = 7) then
                        cntr1 = cntr1 + 1
                    end if
            next
            wday = iFD - cntr1

            'GET HOLIDAYS BETWEEN THE DATE SPECIFIED
            objRSHL.open "select count(holidayid) from holidaymaster where HolidayType = 1 and HolidayDate between #" & CDate(dtFrom1) & "# and #" & CDate(dtTo1) & "#" ,objConn

            if not objRSHL.EOF then
                wday = wday - objRSHL.fields(0).value
            end if
            objRSHL.close

            GetWorkingDay = wday
End Function

--------------------------------------------------
It works fine for almost all date range. Except 15-Jul-05, strange but don't know why.
e.g. if you pass value as 12-Jul-05 to 12-Jul-05 it will return 1
but if 15-Jun-05 to 15-Jun-05 will return 0.

Thankx in advance.

Please do not cross post:
http://p2p.wrox.com/topic.asp?TOPIC_ID=32751

Wind is your friend
Matt

Okay will take care of this.

Does 16-07 and 17-07 return the same results as 15-07?

Maybe you're using a wrong date format and your date is silently converted.

For example 12-7 *could* be december 7, depending on your regional settings. However, 15-07 cannot mean anything else than july 15th.

The conversion can be done by the database if you're not aware of it. Try to use the ISO format (YYYYMMDD) and see if that makes a difference....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

No, 16-07 and 17-07 returns 1.
If I be using wrong date format, it should not work for other dates, i.e.14-07 and 16-07,etc.

Can you work out with this function and check at your end so that you can find out what is wrong exctly.

Thanks in advance.

Hi Rupen,

Not just for 15th, looks like it does behave that way for any fridays.

When you mention 14-07 and 15-07 there, what is the result expected? If that is to be 2, I am afraid if that works, coz, I see 1 for that.

I think the catch is here.
Looks like the week starts there with saturday as 1, thus sunday is 2, not 7 This did the trick. I got to stick to this, as 13-07 was showing 5 as value of d within the FOR loop, when i=0.

BTW, I had to comment the DB related lines, as I don't have any idea what it contains in the holidaymaster table. I assume you have the holidays other than saturday sundays in it. So that shouldn't be a problem for the dates I used.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

You are right it is not counting any Friday. But I am amazed, why so?

For 14-07 to 15-07 it should return 2, and not 1.

For if (d = 1 or d = 7) then
1 refers to Sunday and 7 is to Saturday,

Pls refer this:
http://powerasp.com/content/new/vbscript-constants.asp

Now, the catch is here:

          for i = 0 to iFD

It should be:

          for i = 0 to iFD - 1

This will work fine!!!

Pleas pass your view on this.









Rupen Anjaria.
We CAN'T avoid problems, but of course can solve it.