Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    That Date Thing Again (A2k SR1)

    I ask, how many times has this been covered ?
    Any way.
    What I want to do is to calculate a monthly target. To acheive this I must determine the first date of the current month & the last date of the current month. I think by using the date serial function.

    Is this format like so : DateSerial(Year(Date()),Month(Date())+1,0) (I think this is correct for the last of current month)
    This returns #Name?

    How can I utilise the following code to achieve the amount of days less Sat & Sun.

    *********** Code Start **************
    Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
    ' Note that this function does not account for holidays.
    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer

    BegDate = DateValue(BegDate)

    EndDate = DateValue(EndDate)
    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0
    Do While DateCnt < EndDate
    If Format(DateCnt, "ddd") <> "Sun" And _
    Format(DateCnt, "ddd") <> "Sat" Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop
    Work_Days = WholeWeeks * 5 + EndDays
    End Function
    '*********** Code End **************


    The rest of the calculations I can acheive given that I have the above information.

    Thanks
    Dave

  2. #2
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    try the following

    Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
    ' Note that this function does not account for holidays.
    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer

    BegDate = DateValue(BegDate)

    EndDate = DateValue(EndDate)
    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0
    Do While DateCnt < EndDate
    If WeekDay(DateCnt) <> vbSunday And _
    WeekDay(DateCnt) <> vbSaturday Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop
    Work_Days = WholeWeeks * 5 + EndDays
    End Function

    this does not count the first day, but counts the last day. Use the weekday function.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: That Date Thing Again (A2k SR1)

    Hi Dave,

    The function Work_Days you posted is OK, although the version Cindy posted is more efficient (the Format function is very slow).
    Your expression for finding the last day in the month is also correct. To find the number of working days in the current month in VBA:

    Dim lngDaysInMonth As Long
    lngDaysInMonth = Work_Days(DateSerial(Year(Date()), Month(Date()), 1), DateSerial(Year(Date()), Month(Date()) + 1, 0))

    If you want to use this expression as control source of a text box, place = before it:

    =Work_Days(DateSerial(Year(Date()), Month(Date()), 1), DateSerial(Year(Date()), Month(Date()) + 1, 0))

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    <P ID="edit" class=small>(Edited by D Willett on 11-Oct-02 11:22. )</P>(Hans, i think we posted at the same time)



    Thanks Cindy but I need to go one step at a time.
    I have two unbound texts on a form.
    In the first text I want the first date (short format) of the current month.

    In the second I want the last date of the current month.

    My DateSerial doesn't work and returns #Name?.

    I put the statement in the control source.

    Regards Dave

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    Dave,

    I would do something like the following:

    Given dteCurrent is the day in the month you want to find the start of month and end of month for.

    dteStartofMonth = date(year(dtecurrent),month(dtecurrent),01)

    dteEndofMonth= dateadd('d',-1,date(year(dtecuurent),month(dteCurrent)+1,01))

    the end of the month deducts 1 day from the start of the next month

    Hope this helps

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    Ok
    I copied Cindy's code into a new module and applied the +Dateserial from hans and now returning a date.
    Problem is it returns 21/01/00 instead of the current month.
    Is there a format problem here

    Thanks
    Dave

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    Slow down, we seem to be posting at the same time, I'll catch up in a minute.

    Thanks
    Dave

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    See the attached ??
    Attached Images Attached Images

  9. #9
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    Dave,

    I am unsure what is wrong, please give an explanation what is wrong and I can then have a look. Better still attach the/a mdb with the code in and I can do any alterations required.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: That Date Thing Again (A2k SR1)

    You can calculate the last day of the current month like this:

    DateAdd("m",1,Date()) - day(date())

    Note that it will need tweaking to handle February 29 and the last day of months with 31 days.
    Charlotte

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    Thanks Hans & Charlotte for the input.
    Hope I didn't offend any one with the last post. I was getting confused.
    Anyway.

    Cindy I've attached as asked but not included the code, I thought I would leave that to your best judgment.

    Once again thanks everyone.
    regards
    Dave
    Attached Files Attached Files

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    (Charlotte you function works for me,Thanks)


    I seem to be getting somewhere now.

    "First of month" =DateAdd("d",1-DatePart("d",Date()),Date())
    "Last of month" =DateSerial(DatePart("yyyy",Date()),DatePart("m",D ate())+1,1)-1


    On Load "FormWhatever"
    Me.txtResult.Value = Work_Days(Me.FirstOfMonthDate.Value, Me.LastOfMonthDate.Value)

    I have this working now but one little thing, There are 23 work days in october but the code returns 22.
    If I change the system date to November, the correct number appears 21 ??


    Dave

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: That Date Thing Again (A2k SR1)

    Thanks to all, Now sorted.

    I had another module which also takes off pre-determined holidat dates from tblHolidays
    basHolidayDatesWorkDays

    I had it some time ago from Hans.

    Again, Thanks To All

    Dave

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: That Date Thing Again (A2k SR1)

    This function seems to find the last day of the current month without needing any tweaking:
    =DateSerial(Year(Date()),Month(Date())+1,1)-1

    It finds the first day of the next month, then comes back one day.
    Regards
    John



  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: That Date Thing Again (A2k SR1)

    A slight variation on this is:
    =DateSerial(Year(Date()),Month(Date())+1,0)
    Note that either of these work reliably for all months.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •