count all sundays within date range

[kcat]

here's a fun one. given a STARTDate value and ENDDate value, the user sets the QUERYday field by picking a day (Sunday, Saturday, etc) from a drop down menu. She hits the Calculate button and it returns the number of x-days that occur during that range in a calc field, COUNTresult.

So:

STARTdate - 9/1/2002

ENDdate - 10/12/2002

QUERYday - Sunday

would return:

COUNTresult = 6

The practical application is that i have a community center that is open 12-12 on weekdays, 12-8 on saturdays, and is closed on sundays. I need to figure out how many hours the center is actually open during a given date range in order to calculate what our potential capacity is.

i've gotten as far as creating a DAYScalc field that uses the Left(text,size) concatenate trick...

Left(

DayofWeek(QUERYstartdate) &

"

[BobWeaver]

Try this formula:

Int((EndDate-StartDate)/7) +( Mod(EndDate-StartDate, 7) >= Mod(7+QueryDay-DayofWeek(StartDate), 7))

QueryDay is an integer 1..7 representing the day of week where Sunday is 1, Monday is 2, Saturday is 7 etc.

[kcat]

eureka! it works! thanks so much!

mindless me! i used a similar thing for a reservation scheduling system - i just couldn't remember what i'd done or where i'd put the file.

you just indirectly saved our funding for next year!

-kc.