calculating due date to exclude weekends

[mymeshll]

ok, I have a "Due to Manager" field and a "Due to Leg Office" field....the assignment is due to the Manager 3 days before its due to the leg office. I want the date to automatically populate in the "Due to Manager" field (which I did) BUT, I want it to exclude Saturday and Sundays....can anyone help?

[LaRetta]

Which do you want?

 

You want the DueToManager to be 1) 3 DAYS backwards (and exclude weekends AFTER moving backwards 3 days) or 2) 3 BUSINESS days backwards. It will make a difference in the formula (format as Date result):

 

For 1) use: DueToLegOffice - 3 - Position ( "71" , DayOfWeek ( DueToLegOffice - 3 ) , 1 , 1 )

 

For 2) use: DueToLegOffice - Middle ( "533333453333345" , DayOfWeek ( DueToLegOffice ) - 3 , 1 )

 

LaRetta smiley-laughing

[LaRetta]

The calc for #1 bothers me. It is easier but it doesn't follow the same 'style' as #2 and I think it might be cleaner (and better show the thinking) if I used the same approach for both. So here is #1 in the matching style:

 

DueToLegOffice - 3 - Middle ( "200000120000012" , DayOfWeek ( DueToLegOffice ) - 3 , 1 )

 

Each number in Middle() is day of week starting on Sunday. The span allows looking ACROSS the '7 is Saturday and 1 is Sunday' barrier - a hinderance when using DayOfWeek() in math (your number could end up a minus and there is NO DayOfWeek minus; you could end up with DayOfWeek 22 and there is NO DayOfWeek 22). One indicates the number of days to subtract (or add) and that number position is pulled. Technique learned from Comment; however, if I have twisted the logic, over-convoluted or broke this theory then it's all MY calc. smiley_cool

 

L

[comment]

Oh, no - that's not my style at all. This type of "lookup pre-calculated result" is early FMI, pioneered by Jason DeLoose and/or JMO. I prefer actual calculations.

 

In any case, it doesn't seem right: DayOfWeek() has only 7 possible results, but your string has 15. DayOfWeek (date) - 3 will be negative on Sunday and Monday, and zero on Tuesday. But Middle() does not work well with negative or zero start parameter - it reverts to 1 instead.

 

To follow the logic of the calc (if one can call it a calc), you precompute the 7 possible offsets (I am speaking of your #2 now):

Sunday -> Wednesday: -4

Monday -> Wednesday: -5

Tuesday -> Thursday: -5

Wednesday -> Friday: -5

Thursday -> Monday: -3

Friday -> Tuesday: -3

Saturday -> Wednesday: -3

 

So now it can be:

YourDate - Middle ( "4555333" ; DayOfWeek ( YourDate ) ; 1 )

[LaRetta]

You are right ... Sunday drops a day, Monday drops two! I didn't know Middle() would default to 1 on the first position but it sure makes sense. Would this be true on other text functions as well? Now I question all of them. The 8.0v3 upgrade says:

1.1.1. Fixed a problem where the Position function would incorrectly return the position for a given search string if the starting position was greater than the length of the string being searched and the search string was the last character, or in some cases, if the occurrence was an invalid negative value.

 

I have a lot more testing to do. Thanks for catching me! smiley-laughing

 

L

[comment]

I didn't know Middle() would default to 1 on the first position but it sure makes sense.

 

It doesn't make much sense to me. Middle ("abc" ; 4 ; 1 ) returns "" as expected. Why should Middle ("abc" ; 0 ; 1 ) return "a"?

 

Position() is a bit more reasonable, esp. after the fix you mentioned.