Workdays Without Weekends/Holidays...Pos/Neg Values

[Dboyce]

Hi all,
A frequent lurker, but a seldom poster.

I have read much about Workdays user functions that screen out weekend and holidays. The examples posted are understandable.
All those I have found do not deliver positive and negative results, rather absolute differences.

I am seeking a DateDiff type function that will deliver positive and negative values with weekends and holidays removed and that is not inclusive of the beginning date.

Any suggestions as to prior solutions?

Thanks,

Dan

 

[HiTechCoach]

It probably is possible to modify one of the code examples that you have found that basically works the way you want to give you a negative value.

What code are you wanting to use?

 

[hk1]

I don't think I understand what output you are expecting. Do you expect a number, like a long number or integer, something with decimals? When or why will it show negative? Can you post an example of a date range together with your desired our expected output for your given example data range?

 

[Dboyce]

Hi all,

A little more explanation...

I am tracking the delta days (both positive and negative integers) between the date a job arrives in our facility and when it is promised to ship to the customer.

Ideally, all jobs arrive before their schedule ship date, but when they don't we note a negative number of days between job arrival and promised ship date.

For example...
If a kit arrived on Monday December 20th and was promised to ship on Monday December 27th, the expected number of days would be 4 (subtracting our December 24th holiday and the weekend days of 12-25 and 12-26).
Conversely, if a kit arrived on December 27th, but was promised to ship on December 20th, the expected number of days would be -4 (same subtractions).

I am having difficulties with the examples I have found of integrating negative number results while excluding day one of the process.

Any suggestions, either a modified Workdays user function or possibly a DateDiff function in which I could list (and then subtract) weekends and holidays?

Dan

 

[hk1]

Well, I'll give you an idea how I would build this, conceptually. You need to create your own function that requires two date arguments and outputs a long number. One argument will be the arrival date and the other will be the ship date. (You'll have to pass these in when you call the function, obviously.)

You'll need to loop through every day in the date span and run it through two functions, IsWeekend and IsHoliday. If both are false you'll add 1 to your day counter variable. You can find IsWeekend and IsHoliday functions here:
http://www.tek-tips.com/faqs.cfm?fid=4531

Write your loop so that it always takes the earlier date that is passed in the arguments, no matter which one it is, and loops to the latest date.

After the loop has been through all the days it will either be zero or a positive number. Next, you need to check to see if ArrivalDate > ShipDate. If true, then take your accumulated day count and multiply it by -1.

Does this make sense?

 

[HiTechCoach]

I use this: fNetWorkdays and fAddWorkDays

Instead of trying to modify the function to return a negative value I would make a wrapper function that check to see if the first date is after the second date passed then multiple by result -1.

 

[Dboyce]

Thanks for both of your advice.
I'll play more with this and see where I go.

Dan