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

    Date Calculation (A2000(SR1))

    I need to calculate the difference between two dates less Saturday's and Sundays.

    e.g
    The difference between Monday 01/04/02 & Monday 08/04/02 will be 5 days.
    Just the syntax will help me regarding nulls of course.
    Dave

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

    Re: Date Calculation (A2000(SR1))

    Hi Dave

    See here for a simple function to calculate date differences excluding weekend days.

    See here for a series of much more complicated functions that also take holidays into account.

    There are several threads in this Forum on workdays and holidays, e.g. those starting at <!post=Post 39610, 39610>Post 39610<!/post> and <!post=Post 67566, 67566>Post 67566<!/post> and <!post=Post 114067, 114067>Post 114067<!/post>.

    Regards,
    Hans

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

    Re: Date Calculation (A2000(SR1))

    Hans

    I didn't think this was going to be difficult. This is what I have and need.
    My DB is for Holiday's

    The table (tblPeriod) contains 3 fields.
    [Operative]-Text format
    [FromDate]-Short Date format
    [ThruDate]-Short Date Format

    I simply need to calculate how many days each operative has had in total.
    It seems post 110467 is the correct way for me, but how to implement it is the task ahead.
    Can I create a pop up form with 2 fields
    [Operative]
    [CalculationOfDays]

    Any help would be appreciated (walk thru or demo)

    Dave

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

    Re: Date Calculation (A2000(SR1))

    Dave,

    Create a new standard module. Copy the following code into it:

    Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
    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) <> 1 And WeekDay(DateCnt) <> 7 Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop
    Work_Days = WholeWeeks * 5 + EndDays
    End Function

    Create a query based on tblPeriod.
    Add Operative to the query grid from the field list.
    Next, add a calculated field:

    CalculationOfDays: Work_Days([FromDate], [ThruDate])

    You can use this query as record source for a form.

    Alternatively, you can put an unbound text box on a form and set the control source to

    =Work_Days([FromDate], [ThruDate])

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

    Re: Date Calculation (A2000(SR1))

    Thanks Hans, Perfect.

    Dave

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

    Re: Date Calculation (A2000(SR1))

    Hans

    if I have 01/04/02 to 01/04/02, the query returns 0 instead of 1, can the module be modified

    Dave

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

    Re: Date Calculation (A2000(SR1))

    Thankyou once again Hans.

    This does exactly as I need.

    Regards
    Dave

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

    Re: Date Calculation (A2000(SR1))

    Dave

    Originally, you stated that you wanted the number of (work) days between 01/04/02 and 08/04/02 to be 5. (Note for others: we're using dd/mm/yy format here)

    From this, I inferred that you wanted to calculate differences as follows:

    <table border=1><td>Monday 01/04/02</td><td>0</td><td>Tuesday 02/04/02</td><td>1</td><td>Wednesday 03/04/02</td><td>2</td><td>Thursday 04/04/02</td><td>3</td><td>Friday 05/04/02</td><td>4</td><td>Saturday 06/04/02</td><td>4 (weekend)</td><td>Sunday 07/04/02</td><td>4 (weekend)</td><td>Monday 08/04/02</td><td>5</td></table>
    If you want to include the starting and ending day in the count (like the Excel function NetWorkdays), replace < by <= in the statement

    While DateCnt < EndDate

    Edited to correct stupid typo

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

    Re: Date Calculation (A2000(SR1))

    In the calculated expression field of the query, is it possible to set a constant range ie

    Between Format("#01/04/" & Year(Date()) & "#") And Format("#31/05/" & Year(Date()) & "#")

    This is to calculate => 01/05(current Year) and =< 31/05(current year)

    This needs to stay constant so as not to re-design the query each year.

    I've tried this expression but it doesn't work.

    Dave

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

    Re: Date Calculation (A2000(SR1))

    Try the DateSerial function. It takes year, month and day (in that order) as arguments and returns a date value.

    Between DateSerial(Year(Date()), 4,1) And DateSerial(Year(Date()), 5, 31)

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

    Re: Date Calculation (A2000(SR1))

    Sorry Hans, this is not working.

    The original query works just fine.
    Our holiday rota runs between 01 April and 31 May.
    Our Operatives are allowed to book 20 days off other than bank holidays etc.
    When the calender rolls over, the query will pick up all records instead of between the dates of the rota.

    Does this help ?

    Would it help having 2 unbound texts for the rota dates and setting the criteria to these instead.

    Dave

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

    Re: Date Calculation (A2000(SR1))

    I'm sorry - I don't understand what you want.

    In the post that started this thread, you wanted to calculate the number of days between two dates. The modified Work_Days function will do this. Now, I get the impression that you want to select records based on date criteria, or count them.

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

    Re: Date Calculation (A2000(SR1))

    Sorry to have confused the issue, it was an after thought which should have been made clear in the first instance.

    Ok, I'll try more descriptively

    The module and the query works ok and calculates correctly.
    Now, The operatives apply their holidays in the following manner.

    Mick Castrey, 01/06/02 to 14/06/02 (this is one of his holidays booked)
    he may book another holiday
    Mick Castrey, 15/08/02 to 18/08/02

    by grouping in the query the operative name and summing the calculation , I get the correct calculation. 12 days.

    Our holiday rota starts 01/04 to 31/03 the following year, so this years would be 01/04/02 to 31/03/03.

    The query as it stands now, will pick up any date in the table.

    So I need the query to perform the calculation in the current holiday rota.

    The Module and Query we created in the first place was never wrong other than we need to set a constant in the criteria.

    I tried to put this in the criteria but I think the prob is at the end of the expression, it deals with 01/04(this year) and 31/03(this year) instead of next year. I think I need a +1 somewhere.
    The question is will this work in the query we created ?
    Between format("#01/04/" & year(date()) & "#") and format("#31/03/" & year(date()) & "#")

    Is this a bit clearer

    Dave

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

    Re: Date Calculation (A2000(SR1))

    Dave,

    Thanks for the explanation. I understand your question better now, I hope.

    Try

    Between DateSerial(Year(Date()), 4,1) And DateSerial(Year(Date())+1, 3, 31)

    DateSerial(Year(Date()), 4,1) returns the 1st of April in the current year.
    DateSerial(Year(Date())+1, 3, 31) returns the 31st of March in the next year Year(Date())+1.

    Note:
    Through the 31st of December, 2002, the criteria will be: between 1 April 2002 and 31 March 2003.
    From the 1st of January, 2003, the criteria will be between 1 April 2003 and 31 March 2004.

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

    Re: Date Calculation (A2000(SR1))

    Hans
    Seems like we're now on the same wave length.

    Does this account for dd/MM/yy and do I put it in the criteria of the calculated field, I presumed I would.

    I copied and pasted straight in, and it returned nothing when applying the query.

    sorry for all the bother.

    Dave

Page 1 of 2 12 LastLast

Posting Permissions

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