Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Another Date Question

    Good Morning All -
    I appologize up front as I know this has been asked many times but I couldn't find exactly what I needed. This is also a two question post.

    First question: I need to calculate the difference between dates. I found the DateDiff which works great however it includes Weekends, I only want to count Monday thru Friday and exclude Weekends and possibly holidays.

    I have 2 date Fields, "Date of Service" and "Post Date" I need to know the number of Work days between the dates.


    Second Question: This question involves figuring our Payperiod Ending date based on the Date of Service field. We get paid every 2 weeks. So if the Date of Service is as follows:

    Date of Service Pay Ending Date
    1/3/12 1/7/12
    1/10/12 1/21/12
    1/17/12 1/21/12
    1/24/12 2/4/12
    1/31/12 2/4/12
    and so on.

    I am not good with VBA or SQL, so if you can keep it as basic as possible I would be greatly appreciative.
    Thank you!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suspect you may need to write your own calendar data if you want to account for holidays.
    if you want to calculate working days then you need to find out which day is which. forget which but do a google for MS date time functions and you should be able to spot the relevant function(s)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CHI Brian View Post
    First question: I need to calculate the difference between dates. I found the DateDiff which works great however it includes Weekends, I only want to count Monday thru Friday and exclude Weekends and possibly holidays.
    Here's a solution. The function will return the number of days (Monday to Friday) between 2 dates passed as arguments. Excluding holidays is far more complex and usually involves a special calendar table that must be specific to the country where you live (official holidays vary from country to country).
    Code:
    Function WorkDayCount(ByVal ServiceDate As Date, ByVal PayDate As Date) As Long
    
        Dim i As Long
        
        For i = 1 To DateDiff("d", ServiceDate, PayDate)
            If DatePart("w", DateAdd("d", i, ServiceDate), vbMonday) < 6 Then WorkDayCount = WorkDayCount + 1
        Next i
    
    End Function
    Quote Originally Posted by CHI Brian View Post
    Second Question: This question involves figuring our Payperiod Ending date based on the Date of Service field. We get paid every 2 weeks
    You can use the DateAdd function to add a certain amount of time to a date while specifying the unit ("ww" for week):
    Code:
    PayDate = DateAdd("ww", 2, ServiceDate)
    Have a nice day!

  4. #4
    Join Date
    Mar 2009
    Posts
    120
    Quote Originally Posted by Sinndho View Post
    You can use the DateAdd function to add a certain amount of time to a date while specifying the unit ("ww" for week):
    Code:
    PayDate = DateAdd("ww", 2, ServiceDate)
    Thank you for these. The Days issues has been resolved, they don't want to exclude weekends or holidays so that resolved that issue.

    However, the code you gave for the PayEnding date isn't working as I would need it too. It is giving me two weeks from the date of service so I am getting the Following:

    Date of Service = 1/3/11 Pay Ending = 1/17/11 when in fact the Pay Ending for 1/3/11 is really 1/15/11. Our Pay Period ends on Saturday every two weeks. So 1/2-15/11 is a single pay period, the next would be 1/16-29/11, I would need the Dates of Service that fall between those dates to register as the last day of the period or the ending date of the pay period.

    I don't even know if this would be possible.

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Thank all for your help.

    I did it by hand using the Weekending Date I already had. Just went through in excel and looked at the Weekend Date and determined what the Pay End date was for it and just copied it down.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CHI Brian View Post
    I don't even know if this would be possible.
    Never say "never".

    For a given date (ServiceDate), we can compute the date of the first half month (always day 15) or the date of the last day of the month (day 28, 29, 30 or 31):
    Code:
        If Day(ServiceDate) < 16 Then
            PayDate = DateSerial(Year(ServiceDate), Month(ServiceDate), 15)
        Else
            PayDate = DateAdd("d", -1, DateSerial(Year(ServiceDate), Month(ServiceDate) + 1, 1))
        End If
    We can then compute the date of the Saturday before that date:
    Code:
        PayDate = DateAdd("d", -DatePart("w", PayDate, vbSunday), PayDate)    ' Sunday = 1, Monday = 2, etc.)
    Have a nice day!

Posting Permissions

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