SharePoint 2013 - Need formula to calculate the difference between two dates excluding weekends

I have a formula associated with a calculated field in a SP list.  It calculates the difference between two dates and excludes the weekends (assuming weekends are Saturday and Sunday).   However, this formula gets it wrong when FRIDAY is involved.  So If someone requests a Thursday and Friday off work, my calculated field (using the formula below) returns a value of   3 days requested.   If someone requests just a Friday off, my calculated field returns a value of  2.     However, if someone requests a Monday off, the value returned is 1 (which is correct).   Or any series of dates that doesn't include a Friday.   I'm confused as to what I need to do to fix this.  I'm open to a new formula or if you see the problem, please let me know how to fix this formula.

Here is the formula I am using:

=IF(AND((WEEKDAY([End Time],2))<(WEEKDAY([Start Time],2)),((WEEKDAY([Start Time],2))-(WEEKDAY([End Time],2)))>1,WEEKDAY([Start Time],2)<>7,WEEKDAY([Start Time],2)<>6),((DATEDIF([Start Time],[End Time],"D")+1)-(FLOOR((DATEDIF([Start Time],[End Time],"D")+1)/7,1)*2)-2),IF(AND((WEEKDAY([End Time],2))<(WEEKDAY([Start Time],2)),(WEEKDAY([Start Time],2)=7),(WEEKDAY([End Time],2)<>6)),((DATEDIF([Start Time],[End Time],"D")+1)-(FLOOR((DATEDIF([Start Time],[End Time],"D")+1)/7,1)*2)-1),IF(AND((WEEKDAY([End Time],2))=(WEEKDAY([Start Time],2)),(OR((WEEKDAY([Start Time],2)=7),(WEEKDAY([Start Time],2)=6)))),((DATEDIF([Start Time],[End Time],"D")+1)-(FLOOR((DATEDIF([Start Time],[End Time],"D")+1)/7,1)*2)-1),IF(AND((WEEKDAY([End Time],2))>(WEEKDAY([Start Time],2)),(WEEKDAY([End Time],2)=6)),((DATEDIF([Start Time],[End Time],"D")+1)-(FLOOR((DATEDIF([Start Time],[End Time],"D")+1)/7,1)*2)-1),IF(AND((WEEKDAY([End Time],2))>(WEEKDAY([Start Time],2)),(WEEKDAY([End Time],2)=7),((WEEKDAY([End Time],2))-(WEEKDAY([Start Time],2)))<6),((DATEDIF([Start Time],[End Time],"D")+1)-(FLOOR((DATEDIF([Start Time],[End Time],"D")+1)/7,1)*2)-2),((DATEDIF([Start Time],[End Time],"D")+1)-(FLOOR((DATEDIF([Start Time],[End Time],"D")+1)/7,1)*2)))))))

Regards,
LQ
LVL 1
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dhawalsethCommented:
Hi Linda,

I found the below solutions:
- One thing i observed is, you are using [Start Time]- i guess this is the column name.

Try these, if it works for you. (First one is the best bet)

=IF(AND((WEEKDAY(EndDate,2))<(WEEKDAY(StartDate,2)),((WEEKDAY(StartDate,2))-(WEEKDAY(EndDate,2)))>1),(((DATEDIF(StartDate,EndDate,"D")+1))-(FLOOR((DATEDIF(StartDate,EndDate,"D")+1)/7,1)*2)-2),(((DATEDIF(StartDate,EndDate,"D")+1))-(FLOOR((DATEDIF(StartDate,EndDate,"D")+1)/7,1)*2)))


=IF(AND((WEEKDAY(EndDate,2))<(WEEKDAY(StartDate,2)),((WEEKDAY(StartDate,2))-(WEEKDAY(EndDate,2)))>1),(((DATEDIF(StartDate,EndDate,"D")+1))-(FLOOR((DATEDIF(StartDate,EndDate,"D")+1)/7,1)*2)-2),(((DATEDIF(StartDate,EndDate,"D")+1))-(FLOOR((DATEDIF(StartDate,EndDate,"D")+1)/7,1)*2)))-1


If you are a premium member, would want to look at this one as well-
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/MS-SharePoint/Q_22144390.html

Regards,
Dhawal Seth
0
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Thank you Dhawal -
I believe I may have tried these already.   Both do not work with all situations and I really don't know why.       Please check the image attached as it shows 3 separate calculations -- the first column is just subtracting Start Date from End Date and NOT excluding weekends.   The value shown is correct.  The second column is the first formula you show above and the third column is the second formula that you show.   I have not tried the last solution (link) yet, because ...   in my research I found  a short reference to the fact that it could be because I am using a 'calendar list' and the DATEs are not just stored as a DATE, but in DATE/TIME format.

I have tried creating another DATE field to only show the date, without success -- I mean the system bombs as soon as I try to save the new field.   Perhaps you have a suggestion to do that.   Here is what I tried:    Created a new calculated field called 'STARTDATEOnly' and put    =DATE([Start Time])  
But I may have the syntax incorrect -- although I double checked.

Any thoughts?
LQ
Date-Calculation.pdf
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Solution doesn't work when a field is defined as  'date/time'.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.