# Thread: Calculate Days falling in a period - Purpose built function

1. Registered User
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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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.