11 Replies - 4420 Views - Last Post: 03 August 2009 - 10:25 PM Rate Topic: -----

#1 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

count days that fall between a given range of dates

Posted 28 July 2009 - 10:17 PM

 Dim myDate1 As DateTime = datetimepicker1.value
		Dim myDate2 As DateTime = datetimepicker2.value
		Dim myDateToCheck As DateTime = datetimepicker3.value

		'CHECK IF DATE TO CHECK FALLS BETWEEN THE GIVEN MONTHS AND DAY OF THE 2 DATES
		If (myDateToCheck.Month >= myDate1.Month) AndAlso _
			(myDateToCheck.Day >= myDate1.Day) AndAlso _
			(myDateToCheck.Month <= myDate2.Month) AndAlso _
			(myDateToCheck.Day <= myDate2.Day) Then

			MessageBox.Show("The month/day given falls between the range given")
		Else
			MessageBox.Show("The month/day given DOES NOT fall between the range given")
		End If

this is for a single date..but i have list of date ....and i want to check how many days fall from march 20 to apr 10 between jan 4 to apr5..
and count them...thanks...

Is This A Good Question/Topic? 0
  • +

Replies To: count days that fall between a given range of dates

#2 LoveIsNull  Icon User is offline

  • Recovering D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 646
  • Joined: 10-March 09

Re: count days that fall between a given range of dates

Posted 28 July 2009 - 10:26 PM

I just posted something like this, you can subtract the start date from the end date.
Dim DateStart As Date = #5/5/2009#
Dim EndDate As Date = #7/5/2009#
Dim theDays As Integer = EndDate.Subtract(DateStart).Days
MessageBox.Show(String.Format("There are {0} days.", theDays))

Was This Post Helpful? 0
  • +
  • -

#3 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

Re: count days that fall between a given range of dates

Posted 28 July 2009 - 10:50 PM

thanks for ur reply...but i have two datetimepickers 1 and 2 for startholiday and endholiday..
and i have another two datetimepickers 3 and 4 for record calculations..
so i need to check that how many days fall or not between datetimepicker 3 and 4 from datetimepicker 1 and 2...
Was This Post Helpful? 0
  • +
  • -

#4 LoveIsNull  Icon User is offline

  • Recovering D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 646
  • Joined: 10-March 09

Re: count days that fall between a given range of dates

Posted 28 July 2009 - 11:58 PM

Hmm, this should be a start a least try it out with some different values:
		Dim DateStart As Date = #4/1/2009#
		Dim EndDate As Date = #4/30/2009#
		Dim spanStart As Date = #4/1/2009#
		Dim spanEnd As Date = #5/20/2009#
		Dim theDays As Integer = EndDate.Subtract(DateStart).Days

		If DateStart.CompareTo(spanStart) <= 0 Then
			'DateStart is before spanStart
			theDays = spanEnd.Subtract(spanStart).Days
			If EndDate.CompareTo(spanEnd) < 0 Then
				'End date is before
				theDays -= spanEnd.Subtract(EndDate).Days
			End If
		ElseIf DateStart.CompareTo(spanStart) > 0 Then
			'DateStart is after 
			theDays = spanEnd.Subtract(DateStart).Days
			If EndDate.CompareTo(spanEnd) < 0 Then
				'EndDate is before
				theDays -= spanEnd.Subtract(EndDate).Days
			End If
		End If

Was This Post Helpful? 0
  • +
  • -

#5 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

Re: count days that fall between a given range of dates

Posted 29 July 2009 - 12:39 AM

okai..this seems to wrok but it gives me one day less...for eg:
if stardate is july 13 and enddate is july 15
and spanstart is june 1 and spanend is july 30 then the result should be 3 becoause 13,14,15 falls between this range..but i get only 2...i tried with other dates but results one day less...why is that???
Was This Post Helpful? 0
  • +
  • -

#6 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

Re: count days that fall between a given range of dates

Posted 29 July 2009 - 01:04 AM

and also if i select july 3 and july 3 as startdate and enddate then between range july 1 and july 13 it should result as 1..but it gives 0...
Was This Post Helpful? 0
  • +
  • -

#7 LoveIsNull  Icon User is offline

  • Recovering D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 646
  • Joined: 10-March 09

Re: count days that fall between a given range of dates

Posted 29 July 2009 - 01:38 PM

Well we have to consider how subtraction works here and then consider the technicalities. Essentially, as in your above scenario of July 3 and July 3, how many days are between those dates? It could be argued that there are zero days between them because it is the same day!
The same goes for the first scenario, of July 13 and July 15. This works by subtracting the low date from the high date and adjusting accordingly, thus returning the number of days "in between". So when you subtract July 13 from July 15 you get 2 days because that is just how math works.
If you want to explicitly count the starting day, the solution should be easy, just add 1 to your total.
Was This Post Helpful? 0
  • +
  • -

#8 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

Re: count days that fall between a given range of dates

Posted 30 July 2009 - 09:21 PM

if it doesnot fall between the range then it will display 1 when i add 1 to make it my way...
just want to check it and count...
Was This Post Helpful? 0
  • +
  • -

#9 LoveIsNull  Icon User is offline

  • Recovering D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 646
  • Joined: 10-March 09

Re: count days that fall between a given range of dates

Posted 30 July 2009 - 09:51 PM

Well obviously check that it is within the range first.
Was This Post Helpful? 0
  • +
  • -

#10 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

Re: count days that fall between a given range of dates

Posted 30 July 2009 - 11:56 PM

okai..that worked well...but going further i got another problem...i have more than two ranges in my database table...the above is for only one startend range and one span range...but i have to check all the ranges that are in my database...i tried to loop but gone somewhere wrong.....first i need to check from jan 3 to jan 5 and also from feb 4 to feb 7 between jan 1 and feb 30 for both ranges..and add both the results...ie 3+4 and get 7...
Was This Post Helpful? 0
  • +
  • -

#11 LoveIsNull  Icon User is offline

  • Recovering D.I.C Addict
  • member icon

Reputation: 52
  • View blog
  • Posts: 646
  • Joined: 10-March 09

Re: count days that fall between a given range of dates

Posted 31 July 2009 - 01:18 PM

I don't know exactly what implementation it is that you're using, but put it into a function like this:
	Private Function CountDaysInRanges(ByVal startRange As Date, ByVal endRange As Date, _
	ByVal startSpan As Date, ByVal endSpan As Date) As Integer
		Dim theDays As Integer = endRange.Subtract(startRange).Days

		If startRange.CompareTo(startSpan) <= 0 Then
			theDays = endSpan.Subtract(startSpan).Days
			If endRange.CompareTo(endSpan) < 0 Then
				theDays -= endSpan.Subtract(endRange).Days
			End If
		ElseIf startRange.CompareTo(startSpan) > 0 Then
			theDays = endSpan.Subtract(startRange).Days
			If endRange.CompareTo(endSpan) < 0 Then
				theDays -= endSpan.Subtract(endRange).Days
			End If
		End If

		Return theDays
	End Function


Now you can just call the function as many times as you need to, passing in different dates and you'll have the result in another integer variable.
Was This Post Helpful? 0
  • +
  • -

#12 anirai  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 40
  • Joined: 14-May 09

Re: count days that fall between a given range of dates

Posted 03 August 2009 - 10:25 PM

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

		Dim dt As New DataTable
		Dim da As New SqlDataAdapter
		Dim ds As New DataSet
		Dim cmd As New SqlCommand
		Dim dr As SqlDataReader
	   

		Dim _data As New System.Text.StringBuilder
		Try


			cn = New SqlConnection("server=localhost;database=attendance1;uid=sa;password=;")


			cn.Open()
			
			cmd = New SqlCommand("SELECT * FROM leave_approval WHERE applicant='gaurav pakhrin' ", cn)


			dr = cmd.ExecuteReader


			While dr.Read()
				TextBox1.Text = dr(3)
				TextBox2.Text = dr(4)
			   
				Dim DateStart As Date = TextBox1.Text
				Dim EndDate As Date = TextBox2.Text
				Dim spanStart As Date = DateTimePicker3.Value
				Dim spanEnd As Date = DateTimePicker4.Value
				Dim theDays As Integer = EndDate.Subtract(DateStart).Days




				If DateStart.CompareTo(spanStart) <= 0 Then
					'DateStart is before spanStart
					theDays = spanEnd.Subtract(spanStart).Days
					If EndDate.CompareTo(spanEnd) < 0 Then
					   
						theDays -= spanEnd.Subtract(EndDate).Days
					End If
				ElseIf DateStart.CompareTo(spanStart) > 0 Then
					'DateStart is after 
					theDays = spanEnd.Subtract(DateStart).Days
					If EndDate.CompareTo(spanEnd) < 0 Then
						
						theDays -= spanEnd.Subtract(EndDate).Days
					End If
				End If

				TextBox3.Text = (theDays + 1)
			  
				Dim n As Integer = 0
				n = n + TextBox3.Text
				TextBox4.Text = n

			End While


		Catch
		End Try
   

		dr.Close()

		cn.Close()

	End Sub

this is wat i have done..but not getting the addition of all values that are lopped in textboxe.....
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1