Exclude weekends

Question

I am working on a vacation calendar project.

I have a custom lists with 2 columns, one being StartDate and the other NumberOfDaysTaken.

I am using a simple SharePoint formula (=FromDate + [How many Days]) to add the number of days to the start date to populate an EndDate column.

However this formula does not cater for weekends. For example if the start date is Thursday and the number of days is 6, the formula adds 6 days from Thursdays and enters Tuesday as the end date.

How can I exclude weekends from this calculation?

Jay

Answer 1

There's two ways to deal with the question:

1 - StartDate + x days, including weekend days, but if end day is a weekend skip to Monday. I.e. The "x" days include weekend days, but can't end on a weekend day. For example, If StartDate is 8/5/2019 and "x" is 12, the EndDate is Saturday 8/17/2019, and must be adjusted to Monday.  

=[Start Date] + [Days] -1 
  + IF( WEEKDAY( [Start Date] + [Days] - 1, 2) = 6, 2, 0)      *adjust for Saturday*
  + IF( WEEKDAY( [Start Date] + [Days] - 1, 2) = 7, 1, 0)      *adjust for Sunday*

2 - "x" is not in days, but weekdays. If StartDate is 8/5/2019 and "x" is 12, the EndDate is Wednesday 8/21/2019. 12 days, skipping all weekend dates.

=[Start Date] + [Working Days]
 + IF( WEEKDAY( [Start Date], 3) < 5,
      1 + FLOOR( ( [Working Days] - 6 + WEEKDAY( [Start Date], 3) ) / 5, 1 ) * 2,
      7 - WEEKDAY( [Start Date], 2 ) + FLOOR( ( [Working Days] - 1) / 5, 1 ) * 2
      )

The details on how that works is in my book... ;-) but basically this is using division and rounding (FLOOR) to determine the number of weeks and WEEKDAY to test and adjust for weekends.