Salesforce and other SMB Solutions are coming soon. ×

# calculating due date to exclude weekends

## Recommended Posts

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?

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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 )

##### Share on other sites

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

##### Share on other sites

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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Images

• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.6k
• Total Posts
141.4k
×
×
• Create New...