Date Back with WeekEnds (see code)

[dw85745]

Looking for a better/easier routine tol generate a date back including weekends just using an ending date and the number of days back:

Currently using:

Function CalcBegDateTime(dtmLastDate As Date, iDaysBack As Integer) As Date
'Calc an estimated Beginning Date

Dim dtmEstBegDate As Date
Dim iNumWeekDays As Integer
Dim iTotWeekEndDays As Integer

'Get an approximate date from Days Back
dtmEstBegDate = DateAdd("d", -iDaysBack, dtmLastDate)

'Determine total weekend days, if > Week
If iDaysBack > 6 Then
'Next determine number weekdays in this period
iNumWeekDays = HowManyWeekDays(dtmEstBegDate, dtmLastDate) '<<<Function

iTotWeekEndDays = (iDaysBack - iNumWeekDays) * 2

'Subtract weekend days from EstBegDate to get a final begin date
dtmEstBegDate = DateAdd("d", -iTotWeekEndDays, dtmEstBegDate)
End If

'Add time and Return
CalcBegDateTime = CombineDateTime(dtmEstBegDate, #12:00:00 AM#) '<<<Function

End Function

Thanks
David

[dw85745]

bump!

[Nucleus]

I am too lazy to try and reverse engineer the specs from your code. So, can you please tell me what the function should do?

[dw85745]

nucleus:

Function is trying to calculate a beginning date (excluding weekends) if the user knows a date (eg. Now) and the number of days of data they want to look at (chart for example).

This way the user can load the applicable period
from the DB, without having to know a start date.

[Nucleus]

I threw these together, so use at your own discretion, suggesting testing it properly before using either of them.

Function StartDate(ByVal Date1 As Date, ByVal DaysBack As Long) As Date
'Calculate start date going back x days excluding weekends
Dim i&, day&
For i = 1 To Abs(DaysBack)
    Date1 = DateAdd("d", -1, Date1)
    day = Weekday(Date1, vbSunday)
    Do While day = 1 Or day = 7
        Date1 = DateAdd("d", -1, Date1)
        day = Weekday(Date1, vbSunday)
    Loop
Next i
StartDate = Date1
End Function

[T-Bone]

Now I'm not going to post the code here, because I'm too lazy, and although your question is good, it also seems as though you haven't done much research.

Here's *roughly* what you'll need to do:

Public Function AddWorkingDays(StartDate as Date, NumDays as Long) as Date
'NumDays can be a Negative Number (for Subtracting Days)
Dim lngDayCount as Long
Dim dteCount as Date
Dim intMoveDir as Integer, intDay as Integer
 
lngDayCount = 0
dteCount = StartDate
 
'Decide whether we will be adding or subtracting days
If NumDays >= 0 then intMoveDir = 1 Else intMoveDir = -1
 
Do While lngDayCount < Abs(NumDays)
   dteCount = DateAdd("d", intMoveDir, dteCount)
   
   'Make sure we are not on a Weekend Day
   intDay = Weekday(dteCount)
   If intDay = vbSunday Or intDay = vbSaturday Then
      'We Don't want to Increment the DayCounter
   Else
      'Increment Counter
      lngDayCount = lngDayCount + 1
   End If
Loop
 
'Return this now as the Result of the Function
AddWorkingDays = dteCount
 
End Function

There you go eh, I ended up writting the code anyway. And yes, this is a very slow way of doing it, and I'm sure some bright cookie could come up with something a lot faster (and more Mathematical), however it'll get the Job done won't it?

Honestly, I don't know about you but I don't exactly know too many people who are Hellbent on knowing what date it was 1,000,000 Working-Days-ago. Nonetheless, if that's what they want then good luck to 'em.


ps. Nucleus I do believe you will probably want to pass Date1 ByRef, not ByVal in the Function you wrote.

[Nucleus]

If you want a more efficient algorithm, try this, although I think it can be made even faster

Function StartDate(ByVal date1 As Date, ByVal DaysBack As Long) As Date
'Calculate start date going back x days excluding weekends
date1 = IIf(Weekday(date1, vbMonday) > 5, DateAdd("d", -(Weekday(date1, vbMonday) - 5), date1), date1)
StartDate = date1 - ((DaysBack \ 5) * 7 + (DaysBack Mod 5))
If Weekday(StartDate, vbMonday) > 5 Then StartDate = StartDate - 2
End Function

[Nucleus]

Originally posted by T-Bone
ps. Nucleus I do believe you will probably want to pass Date1 ByRef, not ByVal in the Function you wrote. [/B]

No, as I do not want the function to alter the value passed to it.

[dw85745]

Nucleus & T-Bone

Thanks for your efforts and input.

Will try both in the AM. For some reason, when it comes to date/time manipulation the MS way I have a mental block.

For example my understanding MS expresses the date as a number before the period while time is expressed as a number after the period. Yet when I combine a date time sequence you never see it as a number but just the date.time variant.
Where's the number?

You also use to pack a date for more efficienty, but I guess packing is not used anymore in MS view of the world?


Thanks David

[T-Bone]

Originally posted by Nucleus


No, as I do not want the function to alter the value passed to it.

According to your line of Code you set the Value of the Function as such:
StartDate = Date1

Are you trying to pass-back the value they already sent you?


And onto dw85745 question, the answer is its quite simply irrelevant. If MS has designed VB to handle Dates & Times at the same time in the same variable, that's great so be it.

This is why (for display purposes) the Format function is most Valuable.

[Nucleus]

Originally posted by T-Bone


According to your line of Code you set the Value of the Function as such:
StartDate = Date1

Are you trying to pass-back the value they already sent you?

No. I don't think you understand what is going on there. That function actually modifies the value of date1 as it goes so when it gets to the last line of the function, StartDate = Date1 , Date1 no longer holds the same value that was passed to the function. The problem with this approach is that when passing by ref, the function would change the value of the variable passed into it. By passing it by value the function does not alter the value of the date passed into it which is preferable.

To really understand, I suggest you change the declaration and see what happens to the value of the date you pass into the function


dw85745, if you want to see the number, try:

?CDbl(#1/1/2001#)
?CDbl(#1/1/2001 1:45:00 PM#)

[Nucleus]

?Cdbl(#31/12/1999#)
36525
?36525/365.25 = 100

So you see the date is actually 0 at 1/1/1900. Every day grows the number by 1 and fractional parts represent time during a day.

[T-Bone]

Nucleus,

Let's clear a few things up. Firstly we are talking about the Function that you posted approx 8 posts above this one. eg.

Originally posted by Nucleus
I threw these together, so use at your own discretion, suggesting testing it properly before using either of them.

Function StartDate([B]ByVal Date1 [/B]As Date, ByVal DaysBack As Long) As Date
'Calculate start date going back x days excluding weekends
Dim i&, day&
For i = 1 To Abs(DaysBack)
    [B]Date1 = [/B]DateAdd("d", -1, Date1)
    day = Weekday(Date1, vbSunday)
    Do While day = 1 Or day = 7
        [B]Date1 = [/B]DateAdd("d", -1, Date1)
        day = Weekday(Date1, vbSunday)
    Loop
Next i
StartDate [B]= Date1[/B]
End Function

I am quite aware of what ByRef & ByVal do. I have no need of someone (who uses them quite poorly) to try to tell me exactly what they do.

Probably what would be more smarter (with the function in Question) would be to assign the Value that was Passed, to a Temporary Date Variable first & then work with that variable.

Upon the Completion of the Function, return the Value stored in this Temporary Variable. The reason I point this out is that in earlier versions of VB (about version 3 or 4 from memory), the passed variables had a tendency to 'loose' their values, or alternately, when the values were queried they had either a different value in them or they had defaulted back to 0.

Either way, unless you took a copy of the data the moment it was passed to the function/sub, etc. It could (and sometimes in the worst of times) did, loose the passed value. Perhaps that is why ByRef & ByVal were invented. In any case, the method I have just pointed out, has a bit more "Programettiquette" to it than your method.


As usual, glad to be of assistance.

[Nucleus]

T-Bone

You may have had problem with earlier versions of VB, but this is not the case with VB6. I'll explain in even more detail to clear up any ambiguity for other readers of this thread.

In my text book Programming Visual Basic 6.0 by Francesco Balena Published by Microsoft Press on Page 191

The rule that you should stick to is (and it is highlighted from here on in the text book too) always pass those arguments that must be modified by the procedure by reference, and pass by value all the others. This approach mimizes the risk of accidentally modfying the value of a variable passed to the method.

He goes on to say that not following this rule can introduce subtle bugs that are hard to track down not unlike not using option explicit. Further even more subtle bugs can creep in:

When a procedure can access the same memory location by means of two or more different names - for instance, a global variable or a module level variable that's also passed as an argument - that variable is said to be aliased within the procedure. The problem with aliased variables is that they prevent the visual basic complier from generating optimized code that holds variable' values inside CPU registers when it would otherwise be possible to do so. When all variables are passed by value, it isn't possible for the routine to modify a global value through on one of its parameters, and the compiler can produce better code.

Another benefit of using ByVal is that it allows automatic data conversion, where as ByRef does not.

Consequently I would have to disagree with you that I have used ByVal incorrectly at all, and by not using an extra variable in the routine it should be considered more optimized than yours. I can assure you that variables passed in VB6 do not lose their value anywhere in the function, so you can relax about this issue!

The second function I published should be considered more optimized than the first, but there is still room for improvement .

Just out of interest how old are you anyway, the only person I know who knows of VB3 is Guv and he is old!

[T-Bone]

Sorry to dash your hopes here ole' Chap but I'm too old for you & unfortunately already taken. Nonetheless I do appreciate the notion, and don't worry, there are still plenty more fish in the Sea.

Anyway, yes I do know about ByVal & ByRef and why its good to use them. What I didn't know was that you could safely change the Value of a passed Variable that was passed ByVal. Due to some of my previous experiences I've never done it that way before, (as the method I use is generally more backwards compatible). However I think they say for Strings passed ByVal (perhaps only to API, again as I've never tried) the Value of the String can be changed.

As far as VB3 goes, yes I most certainly did do programming in VB3 as well as VB4, 5, 6 & of course I've already got my eye in on VB7 (or VB.NET).

Anyway thanks for teaching an old dog new tricks, although I'm sure VB would simply assign another Variable (transparently, of the same type) for you to manipulate within your function before you pass it back, exit the function, and it (the variable) went off to be collected by the garbage collector.

Also on the topic of Code Optimisation, try to use Constants wherever you can (you can find out the Value of Constants best by looking them up in the Object Browser - accessed using F2), this has a dual purpose.
Firstly your code is easier to read, ie. You'll know exactly what something like "vbSaturday" or "vbSunday" is as opposed to values 7 or 1.
Secondly, using Pre-Defined Constants gives you faster code &
Thirdly, (yes I know I said there would only be two), Should for some unknown reason, the Constant change (ie. Like MS was going to change the value of the Constant True), you'd be OK, becuse you referenced it by its name rather than by its value. - The same as you use the code:

Me.Caption

rather than

frmMyFormName.Caption

that way if the Form's name changes you don't need to edit 100's of instances where you used the Form's previous name, to the new name that you've decided to use.