Business Date Diff Function

[FrymanTCU]

Okay I have tried this a couple different ways and have had no luck. I tried a few built in fucntion but I must be doing something wrong, if not maybe one of you have a VB function would work instead. I tried to search the forum but did not find anything.

So here's my problem, I have a report that will be updated regularly and I have set it up so new items are appended and deletes items that fall off. But I want to age the items on the report by business days from the day they are added. I have a report date tied to each record, then I tried the DateDiff('w', ReportDate , Date()). Which seems to return weeks not weekdays.

Then I tried a DCount function where I have a seperate Date table with business days as a column in the table. DCount("[Time]![workday]","Time",Time!TransDate>[Closure Log History]![Report Date] And Time!TransDate<Now()) but this returns a count of all the records in the Time table or zero.

If anyone knows how to fix these functions or has a VB function to count business you would be my hero. Thanks in advance.

[rconverse]

I have a function that returns x amount of business days from a date. It was originally designed to do exactly what you are looking for and we didn't make too many changes, so I don't think it would be too difficult to change it back.

http://forums.techguy.org/business-applications/658597-solved-controlling-other-applications-through-3.html

Post #39

HTH,
Roger

[FrymanTCU]

Ok I found the code you are referring to, now do you have a tutorial on how to set this up? And where to I define the variable in my report? I am assuming I need to make this a User defined fuction and call it up in the query but this is way over my programing ability...:beg:

[rconverse]

Tutorial?? Hahahaha...I barely know how it works myself!

I call the function from the form in which the other part of my routine runs and then use that date later in a query.



date2 = [Global Functions].PriorBusDays("1")


Where the "1" is how many business days I want to look back, which you won't need. Now, you "just" need to alter the code to report back the number of days between your dates. That being said, Zack helped out a lot with this and may be able to come and rescue us here as well. Unfortunately, I do not have time to make the attempt now, although I will need this exact functionality soon. If Zack doesn't pop in, I can probably tackle it this weekend, but probably not prior, sorry.

Thanks,
Roger