We help IT Professionals succeed at work.

How can I exclude weekends and holidays in datediff ASP.NET function?

Hey Guys,

In my web app, a webuser submits a form and the date/time submitted is recorded in the db. The form is then put on a queue for processing.

Now, when an employee logins into the app and processes the forms on the queue, the time from when the form was submitted minus the time it was processed is recorded via the datediff function.

The problem that I face, is that the date difference is including weekends(non-business) and holidays, thus, producing invalid data when running reports.

For example, if a form is submitted on friday and not processed until monday, the date diff would record more than 48 hours.

How can I detect weekends and holidays using ASP.NET VB?

Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
For weekdays, see below.

For holidays, you will have to modify this code to do a table lookup and see if the date falls between the dates.

Basically what it does is set the FirstDayOfWeek to Monday for the Weekday function, and ignores > 5 (meaning 6 and 7, Saturday and Sunday).

For holidays, you could either, in the loop, do a lookup in your holiday table for a match (before the end whole), and do the DateAdd(), or outside the loop, do a count of holidays between the start and end date and subtract that from the diffdays / diffhours results.
Dim dateSubmitted As Date = CDate("June 5, 2010 1:30:00 PM")
        Dim dateProcessed As Date = Now
        While Weekday(dateSubmitted, Microsoft.VisualBasic.FirstDayOfWeek.Monday) > 5
            dateSubmitted = DateAdd(DateInterval.Day, 1, dateSubmitted)
        End While
        Dim diffDays% = DateDiff(DateInterval.Day, dateSubmitted, dateProcessed)
        Dim diffHours% = DateDiff(DateInterval.Hour, dateSubmitted, dateProcessed)

Open in new window

C# ASP.NET Developer
Top Expert 2010
Commented:
I meant End While (not end whole).

Also, I modified it to do it better. It also has a sample holiday function.

Sub test3()
        Dim dateSubmitted As Date = CDate("June 5, 2010 1:30:00 PM")
        Dim dateProcessed As Date = Now
        Dim counter% = 0
        Dim tempDate As Date = dateSubmitted
        While tempDate < dateProcessed
            If CheckHoliday(tempDate) Or Weekday(tempDate, Microsoft.VisualBasic.FirstDayOfWeek.Monday) > 5 Then
                counter = counter + 1
            End If
            tempDate = DateAdd(DateInterval.Day, 1, tempDate)
        End While
        dateSubmitted = DateAdd(DateInterval.Day, counter, dateSubmitted)
        Dim diffDays% = DateDiff(DateInterval.Day, dateSubmitted, dateProcessed)
        Dim diffHours% = DateDiff(DateInterval.Hour, dateSubmitted, dateProcessed)
    End Sub
    Function CheckHoliday(ByVal someDate As Date) As Boolean
        Dim holidayList As New Collections.Generic.List(Of Date)
        holidayList.Add(CDate("January 1, 2010"))
        holidayList.Add(CDate("July 4, 2010"))
        holidayList.Add(CDate("June 8, 2010")) ' Just for testing
        CheckHoliday = holidayList.Contains(someDate)
    End Function

Open in new window

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Sorry, just noticed a bug.

Change the CheckHoliday function from

CheckHoliday = holidayList.Contains(someDate)

to

CheckHoliday = holidayList.Contains(DateValue(someDate))

Author

Commented:
Thanks you very much amenkes! Excellent script, but how can also include the time within the date for business days

For example, employees start work at 8:45 AM and stop at 5:30 PM, so the earliest they can process a form on the queue is 8:45 am and the latest is 5:30 PM. If a from was submitted on "someDate 7:15:00 AM", but processed on "someDate 8:47:00 AM", DateDiff(DateInterval.Minute, dateSubmitted, dateProcessed) will return 92 minutes. I want it count from 8:45 AM, thus returning 2 minutes.

??
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Ah, now you are getting into business HOURS...

You might have to just do a calculation to see if the time is before 8:45am and adjust it accordingly. But, if someone submits at 5:31pm, it is the same issue, and needs to roll over to the next day at 8:45am. But again, if they submit on Thursday at 5:31pm, and Friday is a holiday, and is followed by the weekend, it needs to be treated as submitted at 8:45am on Monday.

So, if I submit on Thursday at 5:31pm and it is checked at 8:46am on Monday, it would be 1 minute, correct?

If so, then what happens if it is submitted Wednesday at 5:31pm? Is it 24 hours and 1 minute or should it be 8 hours and 46 minutes?

If the 2nd one, you will have to make the loop a little more complicated.

Author

Commented:
Correct,

Basically, i need the following:
 - (date submitted - date processed) --> excluding weekends and holidays, amount of time on queue
 - (date processed - date viewed) --> excluding weekends and holidays, amount of time user took to process

And another question, how do I get the overall difference rather than individual differences:

For example, 1d 3h instead of 1d OR 27h?

Author

Commented:
Disregard the question about the overall datediff...I think I got it

Author

Commented:
Nice script...Thanks