Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Calculate Days falling in a period - Purpose built function

    Hi



    I have a large piece of data which I "was" minipulating in Excel, using complex formulas and some vba, but as I know will be looking at various reports, I have imported them to Acess as tables... And was trying to query the table na dpull the number of days.



    Unfortunately I am struggling trying to use an equivilant "long" formula that I would have used in Excel... see below



    Code:
    =IF(AH33=0.5,0.5,IF(AND(AC33>=$J$1,AC33<=$K$1,AB33<=$K$1),NETWORKDAYS(AC33,AB33),IF(AND(AC33>=$J$1,AC33<=$K$1,AB33>$K$1),NETWORKDAYS(AC33,$K$1),IF(AND(AC33>=$J$1,AB33>=$J$1,AB33<=$K$1),NETWORKDAYS($J$1,AB33),IF(AND(AC33<$J$1,AB33>$K$1),NETWORKDAYS($J$1,$K$1),IF(AND(AC33<$J$1,AB33>$J$1,AB33<$K$1),NETWORKDAYS($J$1,AB33,0)))))))


    It basically counts network days that fall within a period (4 weeks), looking at the courses start and end dates. So in some cases, a course may be 5 days long, with 3 days falling in one period and 2 days in the next...



    I'm guessing I can use a "Case" statement in a module, but struggle seeing how or where to add field names etc.....



    Any pointers at all would be helpful...



    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    easiest technique is to use a calendar, you will need that ina nay even to workout what days belong to what period.

    you can refine that calendar to store working days. so that you know that an employee may have been working for, say 10 consecutive days, in say 3 weeks becuase of public holidays.

    for now just use a simple table which identifies what dates comprises what accounting periods in any one year.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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