asked on

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?

Adam Menkes

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

ASKER CERTIFIED SOLUTION

Adam Menkes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adam Menkes

Sorry, just noticed a bug.

Change the CheckHoliday function from

CheckHoliday = holidayList.Contains(someDate)

to

CheckHoliday = holidayList.Contains(DateValue(someDate))

rmartes

ASKER

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 Menkes

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.

rmartes

ASKER

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?

rmartes

ASKER

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

rmartes

ASKER

Nice script...Thanks