Need help with AddDate Function

Hello everyone,

I am trying to create a program that will gather Date information, based on present date, minus date a project was started on. This program will only count "Workdays" as in Monday - Friday, so I need to create a subroutine to handle this. I have tought of an algorythm but I haven't been able to figure out how to Add the days to the date as I do my loop. This is what I have come up with.

[code]
Function GetWorkDays(Date1 As Date, date2 As Date)
Dim Range As Integer
Dim DayNumbers As Integer
Dim OneMoreDay As Double

OneMoreDay = DateValue(Date1)
DayNumbers = 0
Range = DateValue(date2) - DateValue(Date1)

Do While Range >= 1

If Weekday(Date1) > 1 Or Weekday(Date1) < 7 Then
OneMoreDay = OneMoreDay + 1
DateValue(Date1) = OneMoreDay '<-- This is where I want to "Add One Day" and continue the loop
DayNumbers = DayNumbers + 1
End If

Range = Range - 1
Loop

End Function
[/code]

I have tried everything from AddDate to DateValue functions. AddDate will only "Show" me the next day, but it won't "save" the date into the variable to continue the loop and count only the "WorkDays" for the project. If anyone can help I would greatly appreciate it.

The Darthmoob.

Comments

  • [b][red]This message was edited by dokken2 at 2005-7-27 4:33:32[/red][/b][hr]
    : Hello everyone,
    :
    : I am trying to create a program that will gather Date information, based on present date, minus date a project was started on. This program will only count "Workdays" as in Monday - Friday, so I need to create a subroutine to handle this. I have tought of an algorythm but I haven't been able to figure out how to Add the days to the date as I do my loop. This is what I have come up with.
    :
    : [code]
    : Function GetWorkDays(Date1 As Date, date2 As Date)
    : Dim Range As Integer
    : Dim DayNumbers As Integer
    : Dim OneMoreDay As Double
    :
    : OneMoreDay = DateValue(Date1)
    : DayNumbers = 0
    : Range = DateValue(date2) - DateValue(Date1)
    :
    : Do While Range >= 1
    :
    : If Weekday(Date1) > 1 Or Weekday(Date1) < 7 Then
    : OneMoreDay = OneMoreDay + 1
    : DateValue(Date1) = OneMoreDay '<-- This is where I want to "Add One Day" and continue the loop
    : DayNumbers = DayNumbers + 1
    : End If
    :
    : Range = Range - 1
    : Loop
    :
    : End Function
    : [/code]
    :
    : I have tried everything from AddDate to DateValue functions. AddDate will only "Show" me the next day, but it won't "save" the date into the variable to continue the loop and count only the "WorkDays" for the project. If anyone can help I would greatly appreciate it.
    :
    : The Darthmoob.
    :


    VB has a DateDiff function that will calculate days/weeks/etc between a start/end date. The interval argument of "w" is supposed to return the # for a weekday but it seems to return the # of weeks between dates.
    MsgBox DateDiff("w", "7/25/2005", "8,1,2005")
  • I'm sorry.. but I already found a method to get the difference of a start and end date, what I was looking for is a way to ADD one day to the actual date without using date time picker or the date function. I don't want to change the system date, I want to have a date variable, and add one day as it goes through the loop. I want the loop to check the "Day of Week" to verify and only add days that are weekdays and not those that are weekends, or saturday and or sunday. As it goes through the loop, the subroutine has a start date, I extract the "Day of Week" and get either 1-7, 1 for sunday 7 for saturday. If it is 2-6 it will add workdays, if not it will skip it. I want the StartDate to add 1 day in the loop until it reaches the enddate, figured by getting the range of DateValue(Enddate)-DateValue(StartDate). Almost like a countdown for loop. Everything seems to work great, except the adding one day to the date variable. That is what I need help with. Anyone have any idea how to do this, please?

    Thank you very much for your help
    The Darthmoob

    : [b][red]This message was edited by dokken2 at 2005-7-27 4:33:32[/red][/b][hr]
    : : Hello everyone,
    : :
    : : I am trying to create a program that will gather Date information, based on present date, minus date a project was started on. This program will only count "Workdays" as in Monday - Friday, so I need to create a subroutine to handle this. I have tought of an algorythm but I haven't been able to figure out how to Add the days to the date as I do my loop. This is what I have come up with.
    : :
    : : [code]
    : : Function GetWorkDays(Date1 As Date, date2 As Date)
    : : Dim Range As Integer
    : : Dim DayNumbers As Integer
    : : Dim OneMoreDay As Double
    : :
    : : OneMoreDay = DateValue(Date1)
    : : DayNumbers = 0
    : : Range = DateValue(date2) - DateValue(Date1)
    : :
    : : Do While Range >= 1
    : :
    : : If Weekday(Date1) > 1 Or Weekday(Date1) < 7 Then
    : : OneMoreDay = OneMoreDay + 1
    : : DateValue(Date1) = OneMoreDay '<-- This is where I want to "Add One Day" and continue the loop
    : : DayNumbers = DayNumbers + 1
    : : End If
    : :
    : : Range = Range - 1
    : : Loop
    : :
    : : End Function
    : : [/code]
    : :
    : : I have tried everything from AddDate to DateValue functions. AddDate will only "Show" me the next day, but it won't "save" the date into the variable to continue the loop and count only the "WorkDays" for the project. If anyone can help I would greatly appreciate it.
    : :
    : : The Darthmoob.
    : :
    :
    :
    : VB has a DateDiff function that will calculate days/weeks/etc between a start/end date. The interval argument of "w" is supposed to return the # for a weekday but it seems to return the # of weeks between dates.
    : MsgBox DateDiff("w", "7/25/2005", "8,1,2005")
    :

  • : Everything seems to work great, except the adding one day to the date variable. That is what I need help with. Anyone have any idea how to do this, please?

    Doesn't this work?

    Date1 = Date1 + 1


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • Wow, I am so darn sorry folks.

    All the reading on AddDate and date functions I was reading I guess I complicated everything too much. I NEVER even thought of just adding the date as a simple mathematical option. It worked and the subroutine works perfectly for adding only work days. If anyone wnats me to post the snippet to use let me know. Again thank you Infidel and those that responded for your help, I just about laughed when I read the post seeing how I complicated a rather easy task. Sorry if it was a waste of anyone's time.

    The Darthmoob

    : : Everything seems to work great, except the adding one day to the date variable. That is what I need help with. Anyone have any idea how to do this, please?
    :
    : Doesn't this work?
    :
    : Date1 = Date1 + 1
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    : [code]
    : $ select * from users where clue > 0
    : no rows returned
    : [/code]
    :
    :

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories