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

Date Calculations

P: n/a
G
If one was to calculate say ten days ahead from a certain date but did not
want to include the weekdays in this count, what formula would i use in my
query column heading?
thanks.

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Don't think you can do this directly in a query... However, what you could
do, is loop through the records in the query and calculate the date, then
update a temporary table and base your report/form on the temporary table,
that now holds the newly calculated date....

Dim curDate As Date
Dim addCount As Long
Dim tmpDate As Date

curDate = Now
tmpDate = curDate
addCount = 0
Do Until addCount = 10
tmpDate = tmpDate + 1
If Format(tmpDate, "w") = 1 Or _
Format(tmpDate, "w") = 7 Then
'Saturday and Sunday
curDate = tmpDate
addCount = addCount + 1
Else
'Weekdays - not counting these,
'so do nothing
End If
Loop


"G" <sk**@optusnet.com.au> wrote in message
news:3f***********************@news.optusnet.com.a u...
If one was to calculate say ten days ahead from a certain date but did not
want to include the weekdays in this count, what formula would i use in my
query column heading?
thanks.

Nov 12 '05 #2

P: n/a
"G" <sk**@optusnet.com.au> wrote in message
news:3f***********************@news.optusnet.com.a u...
If one was to calculate say ten days ahead from a certain date but did not
want to include the weekdays in this count, what formula would i use in my
query column heading?
thanks.

This function returns the date of the week day (ie not Sat or Sun) which is
"iDaysToAdd" week days after dtStart

Function AddWeekDays(ByVal dtStart As Date, ByVal iDaysToAdd As Integer) As
Date
Dim dtTemp As Date

dtTemp = DateAdd("ww", iDaysToAdd \ 5, dtStart) ' add whole weeks first
dtTemp = DateAdd("d", iDaysToAdd Mod 5, dtTemp) 'add on part-week days
If Weekday(dtTemp, vbMonday) > 5 Then dtTemp = DateAdd("d", 2, dtTemp)
AddWeekDays = dtTemp
End Function

Tested code.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.