Cacluating end date without including weekends

[mikeh1992]

Hi All,

I am trying to calculate an end date using a "Start Date" and an expected number of days it would take to ship a product ("Expected Transit Time"). However, I am having trouble doing so. I need to find a way to add this expected number of days to the ship date, but skip over any weekend day that may be included.

For example, if the packaged shipped on 3/16 (a Thursday) and had an Expected Transit Time of 4 days, I want to be able to find the day, but NOT include any weekend day in the formula. So for this example the formula would count Thursday, Friday, Monday, and Tuesday as the 4 days in transit and return 3/21 as the delivery date.

Any ideas on how to go about this?

 

[plog]

I saw a thread about this just this week...and last week and the response to both was 'search the forum':

https://www.access-programmers.co.uk/forums/search.php?searchid=11630507

Also, at the bottom of this page will appear 'Similar Threads', one of which looks like its the exact answer you want.

 

[RW00]

I have just had a similar problem. In effect, your week has five days. So, of you take you shipping time and divide by 5 (integer division) and multiply by 7 you will get the number of whole weeks to elapse. Then add the result of modulo division to that answer you will get a total shipping time to add to your shipping date. The you just need to check whether your scheduled shipping date falls on a weekend and adjust accordingly.

Try an iif statement in the select clause. Obviously the field names will need changing, but this should work.

IIf(WeekDay([tPGMDATE].[Date]+((([tTRANSIT].[LT_DAYS]\5)*7)+([tTRANSIT].[LT_DAYS] mod (5))),2)>5,[tPGMDATE].[Date]+((([tTRANSIT].[LT_DAYS]\5)*7)+([tTRANSIT].[LT_DAYS] mod (5)))+2,
[tPGMDATE].[Date]+((([tTRANSIT].[LT_DAYS]\5)*7)+([tTRANSIT].[LT_DAYS] mod (5))))) As [REC_DATE]

 

[plog]

There's no extra points for cramming a ton of code into one line--actually in fact there's a limit on it. When your logic gets that complex its time to make a custom function inside a module and just call it from the query.