I would like help writing a CF that given the following inputs:

StartDate

Interval (in Days)

List of Holidays (from user entered table)

outputs a non-weekend, non-holiday date. That is add 1 to the result if it's a weekend or holiday until it's an acceptable date.

I can find pieces of what I need on B. Dunning, but can't put it together. I can do this with a looping script, but something tells me this is a perfect job for a CF.

Much thanks to any who offer help.

PS:I'm reposting bcs I believe the original was lost in yesterday's server problems.

I am not sure exactly where you're stuck. As you said, you need to add 1 to the startDate parameter at each iteration. You also need to subtract 1 from the interval parameter if startDate is a workday.

When the interval has been depleted, and startDate is a workday, you output startDate. Otherwise you call the function again.

I've never written a recursive CF , so I really don't know how to start. Could post what the calc would look like if you have the time? I'm really not sure how to begin.

Something like this, I suppose:

NextWorkDate ( startDate ; interval ; listOfHolidays )

Let ( [

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

] ;

Case ( 

interval or not isWorkDay ; NextWorkDate ( startDate + 1 ; interval - isWorkDay ; listOfHolidays ) ;

startDate

)

)

Notes:

1. This version only looks forward, and the interval cannot be a negative number.

2. There's no error checking.

3. Since the end of the range is not known until it is computed, the listOfHolidays must include ALL holidays that are equal to or greater than startDate. Similarly, if you adopt the function to count in both directions, you must include all known holidays in the list.

Thank you, comment. I'll give it a test.

Works great, many thanks. Why do you subtract 1 from the start date before you take the mod? I see that anything greater than 5 means it's a weekend, but I don't understand that subtraction.

Uhm... mainly because January 1, 0001 was a Monday, not a Sunday.

hello, hope is is well. Fascinating custom calculation. I wondered if you can help me figure something out with it? I tweaked it to allow flexible work days (StartDate + WorkDays). E.g Tuesday + 3 Workdays = Friday. Friday is a Holiday, so the CF jumps to the following Monday - perfect.

If it is, Tuesday + 4 workdays, the CF still jumps to the following Monday - not the following Tuesday, as I expected. I've tried all manner of additional calcs, workarounds - but something is eluding me. Can you give me a pointer, or any suggestions.

Much appreciated J

See if this helps:

http://www.fmforums.com/forum/showpost.php?post/276772/

hi Comment,

I am trying to get your function to work but i'm only a beginner with functions but have a grasp.

I have created the function as pic attached shows.

How do i now use it within a field calucation? I know i have to setup a table called listofholidays with dates of holidays etc. but how do i reference the function etc.

Any help or a pic would be great if possible

Thanks,

S

Once the custom function is defined, you call it just like any other function.

Hi Comment,

I'm new to calling functions

My calc is (Issued)-(Weeks * 432000)

Is this how i would call the function?

NextWorkDate (Issued)-(Weeks * 432000)

Thanks,

S

I don't understand your calc, but you need to call the function (like any other function) with ALL its parameters. Since this function has three parameters, the call needs to look like:

NextWorkDate ( startDate ; interval ; listOfHolidays )

with each parameter replaced by data or a field name or a variable or another calculation.

Thanks Comment.

I see what you mean now but i still can't get it to work.

I need to have a delivery Weeks field that substracts a delivery required date to give me an order date.

i.e. a shipment is needed on site at a certain date. I now need to subtract a known expected delivery time (weeks) for this shipment to give me an expected date which i should order the shipment.

Is this possible. I have attached the test file i'm trying to get it working.

Any help would be appreciated.

S

Networkdate.zip

This cannot work with the above custom function, because it only looks forward (see the notes in the same post where the function is).

I have switched the function in your file to reverse direction. I have also changed the relationship to the holidays table, to include any holidays before delivery date.

Networkdate2.fp7.zip

Hi Comment,

Thanks so much. That is absolutely brilliant and what i was trying to achieve. I changed the delivery_weeks * 7 to delivery_weeks * 5 in the Z_CalcOrderDate Calucation to calculate 5 working days back per week. Hope i am right here. It seems to work fine with the 5 option.

Thanks again.

Sully

I changed the delivery_weeks * 7 to delivery_weeks * 5 in the Z_CalcOrderDate Calucation to calculate 5 working days back per week.

Yeah, that makes sense.

Thanks again Comment. Much appreciated.

Sully

Hi Comment.

I have come across an issue with the function. I cannont get the function to work with my fields.

I am using timestamp format instead of date field format. Is this the reason perhaps?

Sully

You could call the function as:

NextWorkDate ( [color:red]GetAsDate ( startTimestamp [color:red]) ; interval ; listOfHolidays )

But actually you need to calculate the date in a field of its own anyway - otherwise how will you relate to the Holidays table?

Can i have the Holiday field as a timestamp too and this would solve same?

It's possible that it would work, but really the function expects dates and to be sure it works correctly you should provide the start as a date and the list of holidays as a list of dates.

Thanks Comment. I will check. The only reason i need it as timestamps is that i'm connecting to an SQL database so the fields have to be timestamp fields instead of dates unfortunately

It kind of works but it sees the interval (weeks) field as seconds. So if i have a timestamp entry for the delivery_req date field of 17/07/2008 12:37:54, it calculates the Order Date as 17/07/2008 12:37:39 (Subtracting seconds instead of dates from it)

This is as far as i got comment. Not sure how to proceed now. Could you take a look or is it even possible?

test.zip

I really wouldn't feed the function a list of timestamps, where a list of dates is expected. You could rewrite the function (I'm not going to), or convert the timestamp to date before using it - see attached.

Networkdate3.fp7.zip

Hi Comment,

Thanks for your assistance with this and your attachment.

Is this what you are doing in the enclosed file.

1. Enter Timestamp Date

2. Calc converts timestamp date to date

3. Function adds interval time to date and then converts it back to timestamp

Thanks,

Sully

Yes.

Many thanks for your help Comment