Add 10 workdays to a weekday 1

[Sprowler]

Hi everyone....

I need to find out how to add 10 working days onto a start date, i.e. if the start date is thursday I want to add the following 10 week days onto it and store it in another field, viz:

Startdate = Thursday 01/05/03 (ddmmyy format)
Enddate = 10 weekdays later ( Weds 14/05/03)

or

Startdate= Monday 05/05/03 (ddmmyy format)
Enddate= Friday 16/05/03 (ddmmyy format)

I need this to work out a target date 10 working days from a start date.

I need this for an urgent app, so I will offer many stars for your assistance.

I've looked at the FAQs and suggestions from similar questions (including one of mine), but they calculate the working days between two known dates which is not quite what I need.

Thanks in advance.

 

[johnwm]

The simple way for small numbers is just to use DateDiff and Weekday functions in a loop:
[tt]
Dim lngCount As Long
Dim dtEnd As Date
dtEnd = DTPicker1.Value
Do While lngCount < 9
dtEnd = DateAdd(&quot;d&quot;, 1, dtEnd)
If Weekday(dtEnd) = vbSaturday Or Weekday(dtEnd) = vbSunday Then
Else
lngCount = lngCount + 1
End If
Loop
MsgBox Format(dtEnd, &quot;dddd, dd mmm yyyy&quot

[/tt]
This is off the top of the head, and contains no error checking etc. but should get you started


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[tnguyen03]

...i have written a function that will return nth number of weekday sometimes ago. here it is:

Function NthWeekDate(NthDays As Integer, Optional tmpDate As Date = 0) As Date
Dim i As Integer
Dim n As Integer
Dim myDate As Date
Dim myWeekDay

If tmpDate = 0 Then
'use current date if date did not pass
tmpDate = Date
End If

'
Do While n < NthDays
myDate = DateAdd(&quot;d&quot;, i, tmpDate)
myWeekDay = Weekday(myDate, vbUseSystem)
If myWeekDay > vbSunday And myWeekDay < vbSaturday Then
n = n + 1
End If
i = i + 1
Loop
NthWeekDate = myDate
Debug.Print &quot;weekday is (dd/mm/yy): &quot; & Format(NthWeekDate, &quot;dd/mm/yy&quot
End Function

...i hope it helps!

Have Fun!

 

[MichaelRed]

Since weekdays and workdays are not exactly the same, consider using hte function shown in faq181-261



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Sprowler]

Thank johnwm - You are indeed a star. I've just tried your code out and it has worked perfectly.

Thanks tnguyen03, I didn't try the code cos johnwm's worked perfectly - but I'm very grateful for your interest.

Thanks MichaelRed, but at present non-workdays are not important (sorry the title of my thread was misleading ). Knowing my users, this requirement will change, so I'll look at your example when that happens.

I will get Brownie Points for this, so you all deserve a share, here's a medal for each....

 

[MichaelRed]

Suit yourself. I look at it as 'penny wise - pound foolish' as the saying goes. Implementing the complete soloution is not much more complex than judst doing datediff and fiddling through the days of the week exclusion, and it solve the problem in one shot, rather than revisiting it, probably well after you have forgotten this thread / advice. Implementing the &quot;whole&quot; soloution -and skipping hte holiday part is nothing more than creating hte table - but leaving it blank, or even better, entering the Holidays and Dates for a prior year, just as a reminder of how it should be done. I guess that advice from here is worth what you pay for it, so the &quot;medal' is heartily appreciated.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places