Calculate number of days excluding weekends

I have this formula:
datediff (day,PORel.PromiseDt,RcvDtl.ReceiptDate)

I am working on an vendor OnTime report score card and i want to calculate if an order was late. But i dont want to to include the weekend in that calculation.
Any ideas on how to do this?

Thanks!

There has been lots of discussion date tables around here. Some solutions are more complicated than others. I personally like to find a well populated table that more than likely has all of the dates that I need, then only grab the date field and group by it. It’s a ready made list of dates. An example for past dates would be the LaborHead or LaborDtl table, people have to clock in and out on working days, so the days that wouldn’t be in the list are generally non-working days, and you can filter out others (like weekends) after you have that list. It’s not 100% fool proof, so don’t use it for anything legally binding, or incredibly important, but most of the time it’s a quick an dirty way to capture a vast majority of the cases.

Other discussions have flowed around CTE queries, but run into problems with hitting maximum amount of recursion.

A third solution is to populate a UD table with dates simply for this type of use.

Depending on what you want, any of these could be viable options.

Yea i cant do your first option since we are a 24/7 shop. I dont understand how i could use a UD table to exclude the weekends from the datediff formula.

Yeah you can, getting list is the hard part. Grab the date and make a calculated field for datepart(weekday,date)

image

Then when you bring the subquery into wherever, just set the criteria to not = 1 or 7


image

1 Like

once you get that, you can count rows between the due date and the delivered date.

What relationship connections do i need and to where?