calculating working days in date range 2

[arkadia93]

I have two text boxes, StartDate and EndDate, and I need to be able to calculate how many days within this date range are working days (not Saturday or Sunday). Can anybody point me in the right direction, or better still does anybody have a script that they use for this purpose?

 

[ChrisHirst]

iterate through the date one at a time and count the occurences of midweek/weekend days

if Weekday(testdate) > 1 and < 7 then
    ' add 1 to the working day count
else
    ' add 1 to the weekend day count
end if

(1 = sunday 7 = saturday)

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[arkadia93]

How do you find out whether or not the day is a weekday?

 

[emozley]

That's what the function weekday does.

Response.Write(WeekDay(Date)) will return a number - if the number returned is either 1 or 7 you know that date is not a weekday, it's a saturday or sunday.

 

[arkadia93]

Thanks, I wasn't aware of that function. I'll try it out when I get the chance.

 

[emozley]

You may well find this site helpful

http://www.w3schools.com/vbscript/vbscript_ref_functions.asp

 

[ChrisHirst]

btw the first of my quickly coded pseudo function should be

if Weekday(testdate) > 1 and Weekday(testdate) < 7 then

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[arkadia93]

How would I iterate between 2 dates? I'm thinking that I would subtract the StartDate from the EndDate to get a NumberOfDays variable. But is it possible to write something like :

for(x='12/10/2006'; x<=NumberOfDays; x++)
{

}

(obviously in VBScript, not C#)

 

[emozley]

Use the dateadd function to add 1 day - then loop until the date is the date you want to finish on eg

<%
'I always refer to the date in yyyy/mm/dd format as it seems to be the most reliable but you may want a 2nd opinion on this!

TheDate="2006/01/01"
Do While TheDate<>"2006/12/31"

'Do your check here to see if it is a weekday

'Now add one day
TheDate=DateAdd("d",1,TheDate)

Loop
%>

This would loop from 1st Jan 2006 to 31st December 2006

 

[arkadia93]

Does Do While also execute the variable it is comparing against (i.e. you are OK putting 31/12/2006 and it will loop through this date too, not just up to 30/12/2006)?

 

[emozley]

I don't think so - however the best thing to do when working with asp is to use Response.Write at various points so you can see the values of your variables.

eg if you put

Response.Write(TheDate) & "<br>"

inside the loop you will be able to see what's going on and whether thedate ever gets to reach 31/12/2006 or if it stops at 30/12/2006

 

[arkadia93]

This is what I have now, but I get the error

"Invalid procedure call or argument: 'DateAdd'"

Can anybody help please?


StartDate = Request("StartDate")
EndDate = Request("EndDate")

'Find number of days
CurrentDate = StartDate

Dim WorkingDays
WorkingDays = 0

Do While CurrentDate <> EndDate

'Check here to see if it is a weekday
if ((Weekday(CurrentDate) > 1) and _
(Weekday(CurrentDate) < 7)) then

WorkingDays = WorkingDays + 1

end if

'Now add one day
CurrentDate = DateAdd("d",1,CurrentDate)

Loop

Response.Write(WorkingDays)
Response.End

 

[emozley]

Can you put a Response.Write(StartDate) and a Response.Write(EndDate) near the top before the loop to see that the variables are being assigned correctly.

I have not seen that syntax before normally I use

StartDate=Request.Form("StartDate")

 

[arkadia93]

I've put Response.Write lines before the loop and both variables are being correctly assigned. If I set the Start Date and End Date as the same then it returns a value, but if I make the End Date greater than the Start Date, then I get this error every time.

 

[arkadia93]

BTW, I am now initialising WorkingDays to 1 not 0, as 0 misses out the first date in the date range.

 

[emozley]

Got it!

The problem line is this one:

Do While CurrentDate <> EndDate

Basically even though you should be able to compare CurrentDate and EndDate automatically as far as vbscript is concerned CurrentDate is date/time format and enddate is probably seen as a string type. Since you can't compare them it continues the loop until you reach a date so high that you can't add another day to it.

Instead you should use:

Do While FormatDateTime(CurrentDate) <> FormatDateTime(EndDate)

This turns both CurrentDate and EndDate into the same data type (date/time) and so you can then compare them and the program works.

 

[arkadia93]

Brilliant! I still have a few more issues with it such as switching from UK to US date, but it looks pretty easy from here on in...thanks!

 

[emozley]

That is why I always work with dates in yyyy/mm/dd - it means there is no chance of confusion between us and uk and if you ever do work with dates in databases I would recommend using yyyy/mm/dd as well or you will get some very unusual results!

Glad it's all working now though.

cheers