By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,463 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

how to calculate the date difference without including sunday

chandru8
100+
P: 145
can any one tell how to do it
iam tried like this but getting answer with including sunday
DateDiff("d","23/02/2008","16/04/2008")
as 53
thanks
Apr 16 '08 #1
Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
find out the number of sundays in the time period and substract from the total numer of days.
Apr 16 '08 #2

chandru8
100+
P: 145
hi
i have tried like this
but this loop works for a month only
if need to calculate for more than 2 months its not working
for ex:
dt = 23/02/2008
todays date
its not working

For i = Format(dt, "dd") To Format(Date, "DD")

If Format(dt, "DDDD") = "Sunday" Then

ii = ii + 1

End If
dt = DateAdd("d", 1, dt)

Next i
Apr 17 '08 #3

smartchap
100+
P: 236
I think following code will do:

'To find out No. of days between two dates excluing No. of Sundays in between them:

Dim StartDay As String

Private Sub Command1_Click()
If Me.DTPicker1.Value > Me.DTPicker2.Value Then
MsgBox "Please select Date FROM which is earlier than Date TO."
Exit Sub
End If
'StartDay = "vb" & Format(Me.DTPicker1.Value, "dddd") 'Used DTPicker3
StartDay = "vb" & Format(Me.DTPicker3.Value, "dddd")
start = Switch(StartDay = "vbSunday", 0, StartDay = "vbMonday", 1, StartDay = "vbTuesday", 2, StartDay = "vbWednesday", 3, StartDay = "vbThursday", 4, StartDay = "vbFriday", 5, StartDay = "vbSaturday", 6)
d = DateDiff("d", Me.DTPicker1.Value, Me.DTPicker2.Value, start)
'w = DateDiff("w", Me.DTPicker1.Value, Me.DTPicker2.Value, Start)
w = Fix(d / 7)
w1 = d - w * 7
If w1 <> 0 And d >= 7 Then w = w + 1 'if selcted more than 7 days and not full weeks then
Text1.Text = d - w
'MsgBox Day(Me.DTPicker2.Value)
'Text2.Text = Format(Me.DTPicker2.Value, "dddd") ' dd/mm/yyyy")'it is like Sunday or Monday etc.

End Sub

Private Sub DTPicker1_Change()
DTPicker3.Year = Me.DTPicker1.Year
Me.DTPicker3.Month = Me.DTPicker1.Month
Me.DTPicker3.Day = 1
'so that we can find out the first day of month in DTPicker1 from where we want
'to calculate the no. of days upto DTPicker2 (excluding Sundays).
End Sub

Private Sub Form_Load()
Text2.Visible = False
'During loading set DTPicker3 date as 1st of the month in DTPicker1.
DTPicker3.Year = Me.DTPicker1.Year
Me.DTPicker3.Month = Me.DTPicker1.Month
Me.DTPicker3.Day = 1
Me.DTPicker3.Visible = False
Command1_Click 'so that on loading No. of Days is shown.
End Sub

Always Believe in GOD!
Apr 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.