0

I created a team leave calendar on SharePoint and need to calculate the days of leave in a column from start date minus end date excluding weekends. Can you help?

1 Answer 1

0

Have you tried this?

IF(ISERROR(DATEDIF([StartDate], [EndDate],"d")), "",(DATEDIF([StartDate], [EndDate], "d")) + 1 - INT(DATEDIF([StartDate], [EndDate], "d") / 7) * 2 - IF((WEEKDAY([EndDate]) - WEEKDAY([StartDate])) < 0, 2, 0) - IF(OR(AND(WEEKDAY([EndDate]) = 7, WEEKDAY([StartDate]) = 7), AND(WEEKDAY([EndDate]) = 1, WEEKDAY([StartDate]) = 1)), 1, 0) - IF(AND(WEEKDAY([StartDate]) = 1, (WEEKDAY([EndDate]) - WEEKDAY([StartDate])) > 0), 1, 0) - IF(AND(NOT(WEEKDAY([StartDate]) = 7), WEEKDAY([EndDate]) = 7), 1, 0))

Also some more links:

2
  • does this formula calculate friday as a weekend? because if i select a 1 day leave on my tracker from 26th jan which is afriday 12am to 26th jan 11.59pm it counts it as 1 day. and if i add thursday as a leave and not friday example 25th jan it counts it as 2 days. which means in general its adding 1 day to all leaves and making it 2 and for weekends it minusing 1 which makes it left with 1. Jan 17, 2018 at 17:59
  • i need it to calculate mon to fri days only and see how many days of leave a person is taken from the Start time to End Time. Please advise Jan 17, 2018 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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