NextWorkDay

[bcooney]

A couple of months ago, comment helped me out with a custom function for calculating the next work day given a start date, interval and list of holidays. My goal is to add the interval and then push the date until it results in a non-weekend, non-holiday date.

Unfortunately, I just noticed that the custom function doesn't count weekends and holidays. I would appreciate any help modifying the CF so that it counts weekends and holidays and then pushes the date as needed. Thanks.

NextWorkDayTest.zip

[comment]

If I understand correctly, you just want to add n days to a date, then push the result forward to the first available workDate?

If so, you could use simply:

NextWorkDay ( tempDate ; listOfHolidays )

Let ( [

isWorkDay = Mod ( tempDate - 1  ; 7 ) < 5 and IsEmpty ( FilterValues ( tempDate ; listOfHolidays ) )

] ;

Case ( 

isWorkDay ; tempDate ; 

NextWorkDay ( tempDate + 1 ; listOfHolidays ) 

)

)

and call it like this:

NextWorkDay ( startDate + interval ; listOfHolidays )

---

P.S. Why not post this as a follow up to the original thread:

http://fmforums.com/forum/showtopic.php?tid/190174/

[bcooney]

Thank you so much. I knew that I could count on you!

Can you point me to a white paper re recursive CFs bcs I can look at what you did and say, oh yeah, but I draw a blank every time I try it on my own.

Edited January 24, 2008 by Guest

[comment]

I haven't written one yet.

Perhaps the discussion here might help you over your imaginary block:

http://filemakertoday.com/com/showthread.php?t=12316

(the title of that thread is NOT my idea).

[The Big Bear]

Comment

I wish you would write a paper on Custom function.

The biggest part that I cannot grasp is the function parameter. They are called by the function but they do not seems to have any value.

Like the function parameter "Listofholidays" does it have a value and if it does, where do you input this value.

I have read papers from Excelisys on there custom function and still have problem with understanding how function parameter works. I also read the Filemaker manual but it told me very little.

Thanks

Lionel

[comment]

There are two distinct stages here: first you define a custom function, i.e. the name of the function, the parameters and the formula. No values are used in this stage - this is purely an algorithm.

Once you have defined the custom function, you call it in the same way as a built-in function - e.g. in a calculation field. You input values to custom function parameters in the same way as you do for built-in functions.

[bcooney]

Thanks, comment, great thread and good to know I'm not alone in my "block." But, I'm waiting for your book...

BigBear:

Here's how I will use comment's cf. I have a startdate field and an end date field. The end date is a calc = NextWorkDay ( startDate + 10 ; List (HolidayTable::Date) )

Edited January 25, 2008 by Guest

[comment]

Oh, it's a book now... I guess that will take a bit longer.

[The Big Bear]

both of your, thanks for the lesson.

When I see a calculation or a custom function posted on the forum, I try to break them down by steps to see how they work and to understand them better. But some times the old mind just does not work.

Thanks again to both of your.

Lionel

[sullyman]

Trying to create this function but what do i put in for "TempDate" - Is is the date field i wish to do the Calc on

Thanks,

Let ( [

isWorkDay = Mod ( tempDate - 1 ; 7 ) < 5 and IsEmpty ( FilterValues ( tempDate ; listOfHolidays ) )

] ;

Case (

isWorkDay ; tempDate ;

NextWorkDay ( tempDate + 1 ; listOfHolidays )

)

)

[comment]

TempDate is the input date that you wish to move to to the next available work day, if it's not a work day.

[sullyman]

Thanks for reply.

How do you mean Comment?

I have several date fields where each field is calculated by the previous field

example Field 2 is calculated by Calculation Field1 + 432000

How would i implement your function if i don't know the date or am i not understanding the function.

Thanks,

Sully

[comment]

I am guessing you would make you Field 2 =

NextWorkDay ( Calculation Field1 + 432000 ; Your listOfHolidays here )

[sullyman]

Thanks Comment

The first problem i am having is when i go to create th function NextWorkDay.

I do the following steps

1. Go to File, Manage Functions

2. Click New

3. Call Function "NextWorkDay"

4. But when i paste in your code, it is saying the specifid paramater "tempdate" cannot be found.

Am i doing something wrong?

Many thanks

Let ( [

isWorkDay = Mod ( tempDate - 1 ; 7 ) < 5 and IsEmpty ( FilterValues ( tempDate ; listOfHolidays ) )

] ;

Case (

isWorkDay ; tempDate ;

NextWorkDay ( tempDate + 1 ; listOfHolidays )

)

)

[sullyman]

Anyone see how i can place the function into custom function without the error please

[comment]

When you define a custom function, you need to define its parameters too. See sections 5 & 6 in:

Help > Customizing files with FileMaker Pro Advanced > About custom functions (FileMaker Pro Advanced) > Using custom functions (FileMaker Pro Advanced)

[sullyman]

Thanks. Can you just let me know what i should be looking for. I'm only a Novice.

Should i be looking for something to define tempdate?

[sullyman]

Thanks Comment.

I think i have it added now. You mean tempdate and listofholidays are kind of variables.

I placed tempdate and listofholidays in the list of Function parameters and i was able to save the function then.

I will try it and let you know. thanks for your patience

[sullyman]

Hi Comment,

Didn't work. Tried the following but when i place a new record, it immediately fills in all other dates (which are incorrect) automatically without user input and the field it should run the calculation on is empty.

NextWorkday ( Field1 + 172800 ; Holidays::listOfHolidays)

Can you take a look please

[sullyman]

Hi Comment.

Would you have a demo file with this Let Function perhaps so i can study it perhaps?

Any help is appreciated.

Thanks,

sully

[comment]

See if this helps.

NextWorkDayCF.fp7.zip

[sullyman]

Many thanks Comment. Very much appreciated.

I will download same and hopefully it will sink in.

Thanks again,

Sully

[Jerremy]

Hi all,

This is similar to an issue I have been struggling with. I've been playing with comment's CF to see if I could modify it for my situation.

I'd like to take a Start Date (ie. 2/20/08) add a Work Day duration (ie. 10 work days) and get the End Date as the result. These 10 days represent 10 working days (Mon-Fri, non-holiday). So, using my 2 values like so: 2/20/08 + 10 Work Days = 3/4/08.

I am counting 2/20 as a Work Day. So if the Start Date was 2/20 my End Date would also be 2/20 - the task could start and end on the same day, counting as one day.

So, in my example, the 14th day is the End Date. The calc would take the duration and add the 4 weekend dates to get me to 3/4/08.

Thanks for any help - and comment, thanks for providing the previous demo file to learn from.

Jerremy

[comment]

See the first post in this thread, and the original thread:

http://www.fmforums.com/forum/showtopic.php?tid/190174/

And also:

http://www.fmforums.com/forum/showtopic.php?tid/182285/

Edited February 17, 2008 by Guest

[Jerremy]

Hi all,

comment, thanks for pointing me to the other thread. Not sure how I missed it in my searches - but with a few tweaks - it's exactly what I was looking to do.

Thanks for your time.

Jerremy

[Courtney]

I'm wondering if someone can point me in the right direction on a similar issue. In my file, we're inputting deadlines, and then based on several criteria calculating the number of work days needed to do the work, and I'm aiming to get a start date for the project that's not a holiday or weekend.

I found a solution for the weekend issue on FileMaker's knowledgebase, and it works beautifully, but I'm having trouble with a custom function to also account for holidays.

My custom function (modified from the one in this thread) looks like this:

ExcludeHoliday (CheckDay; HolidayList)

Let ( [

isWorkDay = IsEmpty ( FilterValues ( CheckDay; HolidayList) )

] ;

Case (

isWorkDay ; CheckDay;

ExcludeHoliday ( CheckDay - 1 ; HolidayList)

)

)

My calculation field (result=date) is defined like this:

ExcludeHoliday ( Target Completion Date - Int ( (Max Completion Days-1)/ 5 ) * 7 - Middle ( "23456034560145601256012360123412345" ; ( DayOfWeek ( Target Completion Date) - 1 ) * 5 + Mod ( Abs ((Max Completion Days-1)); 5 ) + 1 ; 1 ); List ( Calendar Holidays::Holiday Date))

As I said, it excludes weekend dates exactly as it should, but disregards holidays completely. I'm pretty stumped.

I'd appreciate any suggestions or feedback.

~Courtney

[comment]

I don't think you can 'mix and match' in this way. What you have now is a calc in two parts: the first part disregards holidays and the second part disregards weekends.