Count # of Days in Date Range Taking into Consideration Holidays and Before Anniversary Date

Question

User-269639712 posted

I am trying to calculate of the number dates in a date range.  End date is either end date or, if anniversary date is within date range, anniversary date minus 1.  I also need to not count weekends or holidays in my holiday table.  I get it to work as long as the anniversary date minus 1 is not the holiday date.  I'm thinking I have to loop through the holiday table, but I'm having a brain freeze right now.  I am a "sometimes" programmer and wrote this code years ago.  Help!  Thanks in advance!  Here's my code:

                        intCounts = 1 ' Now we start counting days. 'If you always want to count the first day, set this to 1.

                        Do Until begdates = enddates

                            'First, we find out if this day is a weekday or a weekend.
                            'If weekday, 1 gets added to the number of days.
                            Select Case Weekday(begdates)
                                Case Is = 1, 7
                                    intCounts = intCounts 'Weekend, so nothing added.
                                Case Else
                                    If Weekday(enddates) = 1 Or Weekday(enddates) = 7 Then
                                        intCounts = intCounts
                                    Else
                                        intCounts = intCounts + 1
                                    End If
                            End Select

                            'Now, if this day was a holiday, we take it back off again!

                            Dim vsqldates As String
                            vsqldates = begdates

                            Dim returnhols As Object

                            Using cons2 As New SqlConnection(connection)
                                Dim sqls As String = "Select Count(*) as ct from tblCodesholidays where Holiday = @holidays"
                                Dim command As New SqlCommand(sqls, cons2)
                                cons2.Open()
                                command.Parameters.Add(New SqlParameter With {.ParameterName = "@holidays", .SqlDbType = SqlDbType.NVarChar, .Value = vsqldates})
                                returnhols = command.ExecuteScalar
                            End Using

                            If (returnhols > 0) Then
                                intCounts = intCounts - 1

                            End If

                            returntimes = begdates.AddDays(1) 'We move to the next day.
                            begdates = returntimes
                        Loop

                        workinghourss = workinghourss + (intCounts * 8)
                        textbox2sa2.Text = workinghourss.ToString

Answer 1

User283571144 posted

Hi tjkalb,

I am trying to calculate of the number dates in a date range.  End date is either end date or, if anniversary date is within date range, anniversary date minus 1.  I also need to not count weekends or holidays in my holiday table.  I get it to work as long as the anniversary date minus 1 is not the holiday date.  I'm thinking I have to loop through the holiday table, but I'm having a brain freeze right now.  I am a "sometimes" programmer and wrote this code years ago.  Help!  Thanks in advance!  Here's my code:

According to your description, I couldn't understand your requirement clearly.

Do you mean you want to get the nember of working days in the date range?

  

End date is either end date or, if anniversary date is within date range, anniversary date minus 1. 

I couldn't understand clearly about this line.

Do you mean if the end date larger than the  anniversary date, anniversary date minus 1?

I get it to work as long as the anniversary date minus 1 is not the holiday date.  I'm thinking I have to loop through the holiday table, but I'm having a brain freeze right now. 

I found you have already write the logic to check the date is holiday.

What is your issue now?

Best Regards,

Brando

Answer 2

User-269639712 posted

What I'm trying to do?  I'm trying to calculate how many days an employee has built up in sick time and includes all their sick time except sick time scheduled to be taken or new sick time accumulated after their calendar year during their anniversary year.  So, the code gets the begdate off (which will always be a working day - not weekend or holiday - because previous code only allows this), and then checks to see if the enddate is before the anniversary date.  If before anniversary date, it uses this value in enddate.  If >= anniversary date, it uses the anniversary date minus one.  If enddate is not anniversary date, I have no problem because not weekend or holiday (previous code won't allow it).  But, if it's anniversary date minus one, it could be a weekend day or a holiday.  I got the weekend part to work.  I just am having problems if the endate is the anniversary date minus one and that date is a holiday.

So, for example, my anniversary date is July 5th, 2018.  My current calendar year is July 5th, 2017 to July 4th, 2018.  July 5th minus 1 is July 4th, a holiday.  I'm sick July 3rd through July 5th - 2 days sick because 4th is holiday.  Since I want the sick time in this anniversary year, it would only be July 3rd since July 5th is on or after my anniversary date, so 1 day sick in this anniversary year.  July 4th would end up being the enddate.  My code is giving me 2 days sick for time period July 3rd to July 4th.  I can step through code and see it's 2 days - Starts as 1 day (intCounts = 1), adds one day for July 3rd, and adds one day for July 4th but then subtracts it because it's a holiday,  I just check to see if the date is in the holiday table - if it is, it subtracts one.   I am not going through the holiday table line by line - just counting to see if the value exists (select count).   Adding it up it's 1+1+1-1 = 2, but it's only 1 day.   

If I change the intCounts to zero, it will goof up everything else that works.  Where I give intCounts the value, should I first check to see if there's anything in the Holiday table?  If there is, make intCounts = zero, if not make intCounts = 1.  Or do you have a better idea?

Does this make more sense?