excel.shilor.orgExcel and SharePoint

skip to main | skip to sidebar You can find in this blog related topics. Monday, April 27, 2015 Excluding Weekend Days from Date Calculation in SharePoint Calculated Column. This post is an extension to a previous post dealing with work days. SharePoint 2013 incudes the WORKDAYS function that calculates dates excluding weekends and even holidays, a nice enhancement because in SP 2010 it is quite cumbersome to exclude weekdays in a calculated column. Here is a brief explanation to understand the formula below: The default translation of the function WEEKDAY(Start_Date) is 1 for Sunday, 2 for Monday…. And 7 for Saturday. You can check in Excel and if it is not so in your settings, you will have to do some adjustments to the calculation below. Let’s say we need to add 50 days. This means 10 weeks, 5 work days a week, or: TRUNC(Days_To_Add/5,0) = 10, So we have TRUNC(Days_To_Add/5,0) * 7 = 70 to get the # of days passed. If we have 53 days instead, we need add the extra 3 days or: MOD(Days_To_Add,5) = 3. However, if the extra days fall into the weekend, we need to add 1 or 2 days depending if we need to compensate for the entire weekend or only Sunday. And this depends of the WEEKDAY(Start_Date) and MOD(). If the total of both > 6 than we need to add 2 days for the weekend. But, for Sunday we need only one, so we take back 1 from the total. And, we need to take back 2 in the case it is Sat or Sun, together with MOD = 0. (We didn’t have to add the 2 in the first place, but it was easier to go this way.) So the formula would be : [Start_Date]+TRUNC([Days_To_Add]/5,0)*7+MOD([Days_To_Add],5)+IF(WEEKDAY([Start_Date])+MOD([Days_To_Add],5)>6,2,0)-IF(WEEKDAY([Start_Date])=7,1,0)-IF(AND(OR(WEEKDAY([Start_Date])=7,WEEKDAY([Start_Date])=1),MOD([Days_To_Add],5)=0),2,0) Posted by Unknown at Monday, April 27, 2015 9 comments: Wednesday, May 07, 2014 Adding views or list columns to a SharePoint Survey The following action plan allows extending the SharePoint survey template as a quiz or any combination of a survey and a regular list. When you create a Survey in SharePoint 2010, you are pretty much limited to the questions you added and several available survey views. In the survey's List Settings you can't add views or list columns as in other lists. However, the standard List Settings page is also available, though hidden from a direct access. Click this link for an example of how to construct the link and create a desired new view. The same way you can add columns. Just concatenate the sub-site address with the create column page and the survey list ID to be able to add columns. http://mySiteURL/mySubsite/_layouts/fldNew.aspx?List=%7B3DB4059C%2D03F1%2D4A0C%2DAEF1%2DFBF5369E3473%7D The same reasoning you can apply to other maintenance pages like edit page. Why would you need to add columns? Consider the case where you used the survey as a quiz, and would like to notify the user that he or she failed the test because of 3 or more wrong answers. For this, you just need to create a calculated column that counts the # of wrong answers which will trigger an SPD workflow based on the count. Alternately, create a filtered view and subscribe the item creator. Posted by Unknown at Wednesday, May 07, 2014 6 comments: Sunday, April 21, 2013 Excel (or Office) 2003 Document Doesn't Open from a SharePoint List's Link in a Client Application If you setup a SharePoint Library or list to open in a client application, then the Office document or attachment will open in MS Office 2003 as expected. However, if you open a link from a URL, text or multi-line column, Office 2010 and 2007 will open it in a client application while Office 2003 will open it in a browser. (It would be editable it the browser though.) Switching to the datasheet mode will let you right click the link in URL and text columns and open the doc in client. To make it happen in Windows XP (and Excel 2003 for example: in window explorer / tools / folder options / ,file types, select doc mime type. in advanced, select the 'open' action, and uncheck the 'browse in the same window.' I don't have the combination of Windows 7 and Office 2003, but I found a page that suggests the following for Win 7 and IE9: Open the Browser Select Tools from the Menu toolbar Select Internet Options Select the Advanced tab In the Browsing section, uncheck the Reuse windows for launching shortcuts (when tabbed browsing is off) Click OK http://www.niu.edu/erptraining/PS_browser_settings/ie9.shtml Posted by Unknown at Sunday, April 21, 2013 1 comment: Monday, March 04, 2013 User Permission Issue after Upgrade from MOSS to SharePoint 2010 I upgraded the customer's MOSS 2007 STD to SharePoint 2010 STD. One of the users couldn't make changes to library items any longer. It turned out that her permissions were correct. Also, she was the only user affected. After a thorough research it turned out that something went wrong with her user definition in SharePoint.The only action needed to fix the issue was to remove her user from SharePoint, so that the next time she would enter the site SharePoint will bring her user definition afresh from AD. However, this process is a bit tricky and therefore I decided it worth posting. We used to be able to access the All People list in MOSS from a link, which for some reason is not available in 2010, where you can access only the group list. However, if you add to your domain name the string: /_layouts/people.aspx?MembershipGroupId=0&FilterField1=ContentType&FilterValue1=Person you can access the page, if authorized, and view the user list. for example: https://sharepoint.mydomain.com/_layouts/people.aspx?MembershipGroupId=0&FilterField1=ContentType&FilterValue1=Person (for more details on this step see Geoff Varosky's Blog ) When I clicked that specific user I got an error instead of getting that user details. After the deletion of the entry, the issue was solved. If you can't see the deletion link, you may not be authorized to perform this action. In spite of being a site collection administrator, I couldn't do this operation until I logged in as a farm administrator (or more accurately, with the installation user). Posted by Unknown at Monday, March 04, 2013 1 comment: Older Posts Home Subscribe to: Posts (Atom) My best clip on YouTube How to Enjoy Your Job Followers Blog Archive ▼ 2015 (1) ▼ April (1) Excluding Weekend Days from Date Calculation in Sh... ► 2014 (1) ► May (1) ► 2013 (4) ► April (1) ► March (1) ► January (2) ► 2012 (2) ► June (1) ► April (1) ► 2011 (6) ► August (1) ► July (1) ► April (1) ► February (1) ► January (2) ► 2010 (34) ► December (1) ► October (4) ► September (1) ► August (2) ► July (14) ► May (5) ► April (1) ► March (1) ► February (5) ► 2009 (19) ► December (2) ► November (1) ► October (3) ► September (2) ► August (5) ► July (6) About Me Unknown View my complete profile...