Calculating elapsed time in hours from a date to current date excluding weekends

I need to calculate in hours how much time has elapsed from when a record was created to the current time. The hard part is I need to exclude weekend times from this. 开发者_StackOverflow中文版

I have been working on this for quite a while and I am just lost. Here is what I have so far. Thank you in advance for your help.

   Dim MyStartTime As DateTime = "2010-08-09 07:00:00.000"
    Dim MyEndTime As DateTime = DateAdd("h", -1, Now())

    Dim MyHours As Integer = 0

    If Weekday(MyStartTime) > 1 And Weekday(MyStartTime) < 6 Then

        MyHours = DateDiff("h", MyStartTime, MyEndTime)

    ElseIf Weekday(MyStartTime) = 6 Then
        Dim EndTime
        date1.Date()
        MyHours = DateDiff("h", MyStartTime, MyEndTime)


    End If

    lblHours1.Text = MyHours

What algorithm in VB.NET would best suit this calculation?

---

Here's one method:

Determine the TimeSpan in hours for the entire range.

Determine if the start and end falls on a weekend. Subtract the appropriate hours. If starting on a weekend, remove the hours until Monday at 00:00. If ending on a weekend, subtract hours elapsed since Saturday at 00:00. Then account for any full weekend days falling in between.

This requires implementing 3 methods to return a number of hours to be subtracted. If none, just return 0 on each:

NumberOfHoursBetweenDateAndNextMonday - when starting on a weekend day. NumberOfHoursBetweenDateAndEarliestSaturday - when ending on a weekend day. NumberOfFullWeekendHoursWithinRange - all those days in between.

Dim MyHours As TimeSpan = MyEndTime - MyStartTime

'account for start being on a weekend
If (MyStartTime.DayOfWeek = vbSaturday Or vbSunday) Then
    'subtract number of hours until Monday at 00:00
    MyHours -= NumberOfHoursBetweenDateAndNextMonday(MyStartTime)

End If

If (MyEndTimeTime.DayOfWeek = vbSaturday Or vbSunday) Then
   'subtract number of hours elapsed after Saturday at 00:00
   MyHours -= NumberOfHoursBetweenDateAndEarliestSaturday(MyEndTime)
End If

MyHours -= NumberOfFullWeekendHoursWithinRange(MyStartTime, MyEndTime)

---

I would suggest using the TimeSpan object to calculate the hours.
http://msdn.microsoft.com/en-us/library/269ew577.aspx

Besides that if you could figure out which day of the week it landed on and the number of saturdays and sundays between that then just simply subtract that number x 24

Dim MyHours As TimeSpan = MyEndTime-MyStartTime 

---

Try adding this function if you're using VB.net:

Public Function GetWorkdays(ByVal pstart As Date, ByVal pend As Date) As Integer

    If Weekday(pend) = 7 Then
        GetWorkdays = 7 - Weekday(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + Weekday(pend) - 2
    Else
        GetWorkdays = 7 - Weekday(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + Weekday(pend) - 1
    End If

End Function

You may test this code by:

setting two dates as GetWorkdays(pstart,pend) where as pstart is your start date and pend as your end date. It will calculate the number of days excluding the weekends.

Have it a try. MABUHAY!