0

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

  • Your requirement has a lot of validations which will require recursive checking of weekends. I will recommend you to use powerApps to auto-populate the form or js if you are using classic SharePoint or populate the field using a workflow or MS Flow. – harshal gite Aug 29 '19 at 13:28
0

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.

enter image description here

| improve this answer | |
  • Mike thank you for this. Also can you please give me more details on your book. Is it on Amazon? – Jawalla Aug 29 '19 at 14:40
  • 1
    Yes it's on Amazon: amzn.to/2CpqK87 or search for "SharePoint Calculated Columns and Validation Formulas" – Mike Smith - MCT - MVP Aug 29 '19 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.