0

If anyone know formula without code in SharePoint 2010 to show differens between dates without weekends and holidays, I'm trying to find solution but no result so far ...

I will be veru gratefull for support,

  • I achieved a passable solution using SPD workflows. – Dave Jul 28 '14 at 15:36
3

If you plan to do a calculated column, than indeed there is no out-of-the-box function that would it for you. The closest you could get is using the WEEKDAY to detect which day of a week it represents, and remove those (maybe build multiple calculated columns as support). I did found something close to what you are looking for (EXCEPT HOLIDAYS), maybe it helps:

=DATEDIF([Start Date],[Due Date],"D")-IF(WEEKDAY([Due Date])=7,FLOOR((DATEDIF([Start Date],[Due Date],"D")+WEEKDAY([Start Date]))/7,1)*2,FLOOR((DATEDIF([Start Date],[Due Date],"D")+WEEKDAY([Start Date]))/7,1)*2+1)+IF(WEEKDAY([Start Date])=7,2,1)

With SharePoint designer, if you plan to use this in a customization of a web part, with XSLT you could achieve the same, again, darn complicated - still nothing for Holidays as these are too specific - http://www.sharepointbloggers.com/2009/03/data-view-customization-calculating.html

In InfoPath though is much easier - few pointers http://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/

  • I have already found it yesterday, it works but without holidays ... Thanks for support – Wilon Jan 20 '14 at 8:28
  • As already highlighted Holidays are too specific to each country, business, etc. I would see using the same thinking forward if you managed to establish what a Holidays means (e.g. another column having a certain value). – Marius Constantinescu - MVP Jan 20 '14 at 9:22
  • I have on my mind my local holiday days, I know it and I can set it to some column or custom list, it will be constant list. If after adding those information this case it is feasible? – Wilon Jan 20 '14 at 10:11
  • Yes, of course - still be aware that it will, most likely, apply to your case only. I still believe that introducing in the formula a 3rd column (e.g. a choice column to store the type of Event - one being Holidays - similar to what you find already out-of-the-box with the Category column) would do the trick! – Marius Constantinescu - MVP Jan 20 '14 at 12:24
  • Could you be so kind and help me how it should look like that formula? – Wilon Jan 20 '14 at 12:32
1

There's definitely no OOTB function that can do this. Just consider the different holidays around the world. How could you write a universal formula that takes into account all the different countries and calendar variations? So, I'm afraid you have to resort to some custom code functionality to do the calculation.

  • I don't search solution which will be work around the world. I want to use it in my country. It can be set to custom list or something else. – Wilon Jan 20 '14 at 8:34
0

I achieved this by using a calculated column and a workflow. Users choose a start date and end date. A calculated column calculates the interval and removes any weekend days from the result with this formula:

(=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))))

I then created a list of all holidays, numbering them 1-10

Create a workflow with variables: check date=StartDate and requested days=calculated column.

This will check your start date against your holiday list and subtract a day if there is a match, then it will increment to start date +1 and check again; this repeats until the end date has been checked.

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.