Working Days

[yeddaborg]

Hello all,

I have been searching the archives for answers -- and have found information, but some is a bit old, and I wonder if there might be a newer solution.

What I need to do is fairly simple, and common: Calculate the # of working days only (excluding weekends) between two dates. There are two scenarios: date1 -> date2, and date1 -> current date.

Could anyone advise of a solution or point to an existing topic thread?

many thanks in advance.

julia

[VFXdbGuy]

take a look at http://www.briandunning.com/filemaker-custom-functions/. I am pretty sure there is a custom function there that does just that.

[Lee Smith]

Julia has the regular edition, so a custom function is not an option.

However, there are several solutions on work calculations. You need to consider if you want the days inclusive or not, and to allow for holidays, etc.

Do a search for +Work +Day +Calculations, or +elapse +time and you should find plenty of useful help. Also, BobWeaver posted a solution in Sample Files a long time ago that shows how to work in holidays, and has kept it update over the years.

HTH

Lee

[John Mark Osborne]

There is a file on my web site that calculates work days. Here is the download link:

http://www.filemakerpros.com/WORK.zip

The file was created in FileMaker Pro 4.0 but works fine if you convert 7, 8 or 8.5.

[LaRetta]

Hi Julia,

There is no difference between Date1 and Date2 vs. Date1 and current date. The only difference is that, when using Get(CurrentDate), the calculation must be unstored (in calculation box, select Storage Options and check 'Do not store...'). Most date-span requests are inclusive, ie, From/THRU and not From/To. When viewing a full week on a calendar, User wants to count 5 work days and not begin counting on Tuesday - Friday. This calc is inclusive but if needed, it can be easily adjusted by adding a User global toggle to drop the last day (depending upon whether workday or weekend). If you wish the end date to be [color:blue]Get ( CurrentDate ) then just replace all instances of Date2 with this entire portion in blue (with parenthesis intact).

If ( Date1 and Date2 ) ;

Let ( [

spanStart = Date1 - Mod ( Date1 ; 7 ) ;

spanEnd = Date2 - Mod ( Date2 ; 7 ) + 6 ;

wkndCount = Div ( spanEnd - spanStart + 1 ; 7 ) * 2 - (spanStart < Date1 ) - ( spanEnd > Date2 )

] ;

Date2 - Date1 + 1 - wkndCount

)

)

The span starts at the first Sunday prior to the start date and the span ends on Saturday after the end date. Weekend count is the full weeks in between then boolean test deducts one day if span starts before Date1 (indicating first day of span is Sunday and NOT within the range) and deducts one day if span ends after end date (indicating last day of span is Saturday and not within the range). Once we know the weekend count, we subtract weekend count from total days in the (inclusive) date range.

Oh ... this doesn't require custom function and this is the only calc required.