Difference between two dates while excluding the weekends

[Cory_nz]

Hi people

I'm having a bit of trouble here, and I've tried trawling through previous posts but haven't really found anything helpful.

Problem: I'm trying to put together a formula that will allow me to calculate the turnaround time it's students assessment to be marked.

I have two dates, 'date_received' and 'date_marked', and obviously the turnaround time would be the difference between these two dates.

What I would like to do however, is exclude the weekends in this calculation, so if an assessment was received on the Friday, but marked on the Monday, then the turnaround time would be "1", and not "3".

Anyone have any ideas on how to do this? I'm sure it's relatively easy, but I'm having trouble getting my head around this.

thanks in advance

Cory

[Vaughan]

There are a couple of custom functions on Brian Dunning's web site that will do this: one of them is

http://www.briandunning.com/cf/179

You'll need FMP Advanced to add custom functions to a file.

[Cory_nz]

Hi Vaughn,

thanks for that. What can I do if I don't have Advanced? Any ideas?

[TheTominator]

What can I do if I don't have Advanced? Any ideas?

If you don't have Advanced, then type in or copy/paste the calculation directly each time you need to apply it in a calculated field or script step instead of creating a handy centralized function.

Custom functions can add greatly to the convenience of developing a solution, but they aren't strictly necessary.

[comment]

Custom functions can add greatly to the convenience of developing a solution, but they aren't strictly necessary.

I am afraid you are very much mistaken about that. If a custom function is recursive (i.e. it calls itself, as does the function that Vaughan suggested), you cannot use the same formula in a calculation field.

[comment]

You don't need a custom function for this. Assuming both your start date and end date never occur on a weekend, you could use:

Let ( [

s = StartDate - DayOfWeek ( StartDate ) ;

e = EndDate - DayOfWeek ( EndDate ) ;

w = Div ( e - s ; 7 )

] ;

EndDate - StartDate - 2 * w

)

Note that this doesn't take into account any holidays that might fall in the given range.

Edited August 10, 2009 by Guest
Forgot to warn about holidays.

[Lee Smith]

This topic has come up in the past, just do a search for your Keywords [color:blue]+WorkDay +Calculations (include the pluses as shown), and that should get you going.

If you need to be concerned about Holidays, there are a few posts that show that as well.

Lee

[Cory_nz]

Hi, thanks for that! Seems to work a charm!

Holidays aren't too big an issue for me, just the weekends so this will be fine - thanks!

Don't suppose you could walk me through the code do you? Best if I can learn what you've done, as opposed to just copying and pasting...

Again, thanks everyone for your assistance

/Cory

[comment]

Roughly, it computes the number of weeks between the dates, and subtracts 2 days for each from the elapsed days.