Jump to content
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?

Link to comment
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

Link to comment
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



Link to comment
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 )

Link to comment
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



Link to comment
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.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

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

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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


  • Create New...

Important Information

Terms of Use