Add or Subtract Working Days

[-Queue-]

I was recently asked to provide a calculation that can subtract a number of working days from a given date. The opposite solution is quite similar, so I thought I might as well post the calculations for the benefit of others. Note that these calculations assume a weekend date will not be entered as either dateStart or dateEnd.

Add Working Days:

dateStart + numDays + 2 * Div( numDays; 5 ) + 2 * (DayOfWeek(dateStart) > 6 - Mod( numDays; 5 ))

Subtract Working Days:

dateEnd - numDays - 2 * Div( numDays; 5 ) - 2 * (DayOfWeek(dateEnd) <= Mod( numDays; 5 ) + 1)

For lower versions, use Int(numDays / 5) in place of Div( numDays; 5 ).

[comment]

I have started in much the same direction, but in addition to the anomality of starting at weekend I wanted to make it more general, i.e. adaptable to any pattern of working days in a week (see here for such a general formula to compute working days in a range).

However, now that I look at yours, it strikes me that at least the limitation of not starting on a weekend date can be removed simply by shifting the weekend date to the preceding Friday, when adding days - or to following Monday, when subtracting them.

So the formula for adding n working days to date would be:

Let ( [

d = date - Choose ( Mod (date + 1 ; 7 ) ; 1 ; 2 ) ;

w = Div ( n ; 5 ) ;

r = Mod ( n ; 5 ) ;

s = Mod ( d ; 7 ) ;

a = s + r > 5

] ;

d + 7 * w + r + 2 * a

)

[-Queue-]

Anomality, I like that word, even if it isn't a real one. Consider it stolen. Now we are even.

You know, I was searching for a general formula for hours last night. Finally, I gave in and just developed my own.

My calc should probably include an error result if a weekend date is entered though. For its applied purpose, there should be no such entry. However, I do like your all-purpose one. I'm just curious whether a weekend entry, in general, shouldn't raise a warning flag, rather than assuming it should be one day prior or following. If, say, a user entered a wrong month or typed 11 instead of 1, accidentally, which caused a weekend date to be entered mistakenly, I think it would be more common (though I could be wrong) to assume a bad entry than to automatically change the date to an acceptable one.

What do you think?

[comment]

I think those are two separate issues: the formula should be general, and if the specific application requires validation, then either validate the field or add a clause to the formula to flag an error. I can certainly see a situation where say a contract is signed over the weekend, specifying n business days for fulfillment, and the question is when is the date due.

"Anomality" - duh! But yeah, it has a nice ring to it. Would make a decent title for an album.

[-Queue-]

You have a very valid point. Now I just wish you would have given me the calc before I went to all the trouble of developing my own. Honestly, I felt like an elementary student learning the times tables all over again. I have no idea how I ever came up with the calculation in

this thread last year. I guess I just haven't worked with dates in so long I started to lose it...

It would definitely have to be for an acid jazz or Tool CD. Until that time, I am an anomality.

[-Queue-]

Oh! And congrats on breaking 1000 in 6 months! Now you're hot on my trail.

[comment]

I just wish you would have given me the calc before I went to all the trouble ...

Which calc do you mean? The one in the other thread was there all the time. The calc I posted here is yours, except the first line of d = ... . The rest is just a rewrite.

I understand the frustration, though - I am still trying to convert the general formula from Workdays ( startDate ; endDate ) to EndDate ( startDate + n workdays ), but it is proving very hard (for me, at least). I wish Position ( "abcdefg" ; "x" ; 5 ; 1 ) would NOT return zero!!!

[QuinTech]

What about holidays?

?

[comment]

Holidays, in this aspect (of counting n working days from a given date) are another PITA. So far I haven't been able to come up with a universal solution even without accounting for holidays, so...

I am beginning to suspect this will have to be done by a script or a custom function, examining each day in turn. In such case holidays will not be a problem, as long as there is a related table specifying their dates (and with a universal solution there has to be such a table).

[-Queue-]

I performed a variety of searches the other night and never once hit on that thread, which is to what I was referring.

Can you elaborate more on your 'general formula from Workdays( startDate ; endDate ) to EndDate( startDate + n workdays )'?

[QuinTech]

I did a working model of this some time ago. It does involve a related table showing all declared (American) holidays, with the option to mark specific dates as non-working days.

[comment]

This is where I am standing at the moment:

Input parameters are date and n.

Let ( [

pattern = "1000001" ;

b = PatternCount ( pattern ; "0" ) ;

w = Div ( n ; b ) ;

r = Mod ( n ; b ) ;

s = DayOfWeek ( date ) ;

So here we know how many full weeks need to elapse, and we are left with the remainder r. We now want to count r business days starting from s.

Basically we should be looking for the r-th occurence of "0" in a looped pattern, starting at s. But this where things start breaking up: if r = 0, the Position() function will always return 0.

So perhaps we should look for the (s+r)-th occurence, starting at 1? If s = 1 and r = 0, it will find the first "0" at the second position. 2 - s = 1, so we have added one day. So far so good, but if s = 1 and r = 1, it finds the second "0" at the third position, so we are now adding 2 days. This is incorrect - we should still be adding only one day (that a-word again).

In short it's a mess - I have tried all kinds of variations, and I can't get FM to do a simple fingercount.