counting days in a weekday dependant formula 1

[Cordeiro82]

Hello,

I currently have a Query that calculates the number of days that an order is late using

Now()-[tableX].[DateX]

The problem with this is that the number returned is the count of calendar days and not business days. Is there a way to count only business days (Mon-Fri)?

Thanks,

Cordeiro82

 

[traingamer]

Yes. Search this forum for 'business days' or look in the faq section for a method including holidays. Post back if you have a more specific question on the method(s) that you find.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Or a relatively simple solution that doesn't involve looping or modules (but does not account for holidays):

(datediff("w",[start date], [end date]) * 5) + 
(datediff("d",[start date], [end date])) mod 7 + 
((datepart("w", [start date]) < datepart("w", [end date])) * 2)

The first line calculates the number of full weeks and multiplies by 5
The second line gives the number of days left in a partial week
The third line subtracts 2 (accounting for the weekends) if the partial week spans a weekend.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Orion45]

You need to make sure you use a DateDiff parameter to count Monday as the first day of the week.
The correct formula should actually look like this:

=(DateDiff("d",[start date],[end date],2))-((DateDiff("w",[start date],[end date],2))*2)

As Traingamer states this will not account for holidays but it will give users a count that is generally accurate which, is good enough for my needs. Hope this helps.

 

[ciarra41]

Hello this is what I need too, but how can I get it to count the amount of business days elasped in a given month counting back for that date field only excluding weekends and possibly holidays?

Example:
mydatefield busDays
02/27/2008 18 days
01/18/2008 13 days

 

[PHV]

Did you read the FAQ ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886