how to skip weekend dates/days

[Haroon]

hi

i would like to get a code where it skips weekend days, e.g. if i choose 6st
apr, and i want to show the date after 10 days from 1st apr, which will be
17th apr.

the user chooses 6th apr as date, the system checks what date will be after
10 days without counting in weekend days.

textbox1: 6th apr 2009
textbox2: 17th apr 2009

i dont want the code to count 11th & 12th apr as dates, just count the 5
working days.

thanks in advance.

 

[Graham Mayor]

See http://www.gmayor.com/insert_a_date_other_than_today.htm and in
particular the linked Date calculations article
http://www.gmayor.com/downloads.htm#Third_party where you will find this
issue covered on page 7. Do however read the introduction on the first page.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Haroon]

thanks but that did not help

i have calendar with this code:
UserForm1.TextBox3.Text = Calendar1.Value + 14

but i want the code to skip weekends, just show next date as a weekday.

 

[H. Druss]

hi

i would like to get a code where it skips weekend days, e.g. if i choose
6st
apr, and i want to show the date after 10 days from 1st apr, which will be
17th apr.

the user chooses 6th apr as date, the system checks what date will be
after
10 days without counting in weekend days.

textbox1: 6th apr 2009
textbox2: 17th apr 2009

i dont want the code to count 11th & 12th apr as dates, just count the 5
working days.

thanks in advance.

Hi Haroon
Try this::
========================================================
Private Function GetDate(StartDate As Date, NumberDays As Long) As Date
Dim i As Long
i = 1
Do
Select Case Weekday(StartDate)
Case 2, 3, 4, 5, 6
StartDate = StartDate + 1
i = i + 1
Case Else
StartDate = StartDate + 1
End Select
Loop Until i = NumberDays

GetDate = StartDate

End Function
========================================================
Good luck
Harold

 

[Haroon]

where do i put the code?

does it go in
Private Sub Calendar1_Click() ?

i want to select a date from calendar and display it on a textbox1
and textbox2 should display what date will be after 10days from the current
selected date in textbox1.

cheers.

 

[H. Druss]

where do i put the code?

does it go in
Private Sub Calendar1_Click() ?

i want to select a date from calendar and display it on a textbox1
and textbox2 should display what date will be after 10days from the
current
selected date in textbox1.

cheers.

Hi Haroon
This is with a user form with 2 text boxes and a MonthView.
Should give you the idea.
Good luck
Harold

==================================================================
Option Explicit

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

TextBox1.Text = DateClicked

TextBox2.Text = GetDate(DateClicked, 10)

End Sub

Private Function GetDate(StartDate As Date, NumberDays As Long) As Date
Dim i As Long
i = 1
Do
Select Case Weekday(StartDate)
Case 2, 3, 4, 5, 6
StartDate = StartDate + 1
i = i + 1
Case Else
StartDate = StartDate + 1
End Select
Loop Until i = NumberDays

GetDate = StartDate

End Function
=======================================================================

 

[H. Druss]

Hi Haroon
In case you stick with the Calendar Control
==============================
Private Sub Calendar1_Click()
Dim StartDate As Date
StartDate = Calendar1.Value

TextBox1.Text = StartDate

TextBox2.Text = GetDate(StartDate, 10)


End Sub
==============================
Harold

 

[H. Druss]

Hi Hartoon
Revised code. Not too elegant, but does what you want.
Option Explicit
==============================================
Private Sub Calendar1_Click()
Dim StartDate As Date
StartDate = Calendar1.Value

If Weekday(StartDate) = 1 Or Weekday(StartDate) = 7 Then
MsgBox "Must start on a weekday!"
Exit Sub
End If

TextBox1.Text = StartDate

TextBox2.Text = GetDate(StartDate, 10)

End Sub
=================================================
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

If Weekday(DateClicked) = 1 Or Weekday(DateClicked) = 7 Then
MsgBox "Must start on a weekday!"
Exit Sub
End If

TextBox1.Text = DateClicked

TextBox2.Text = GetDate(DateClicked, 10)

End Sub
=====================================================
Private Function GetDate(StartDate As Date, NumberDays As Long) As Date
Dim i As Long
i = 1

Do While i < NumberDays
Select Case Weekday(StartDate)
Case 2, 3, 4, 5, 6
StartDate = StartDate + 1
i = i + 1
If Weekday(StartDate) = 7 And i = 10 Then
StartDate = StartDate + 2
End If
Case Else
StartDate = StartDate + 1
End Select
Loop

GetDate = StartDate

End Function
========================================================
Good luck
Harold

 

[John Howard]

Hi Haroon,

Take a look a the worksheet function 'NETWORKDAYS'.

 

[Haroon]

this dont work
it says

compile error:
Sub or Function not defined

GetDate is highlighted.

 

[Haroon]

Thanks Druss your a star *