Posted on

How to get total week days or business days between dates in Excel

When working with dates in Excel, you can get the number of days between two dates using the DAYS() or NETWORKDAYS() formulas.

  • DAYS() will return the total number of days, including weekends.
  • NETWORKDAYS() removes weekends and only gives you the count of only the business/week days between two dates.

You could use DAYS() to quickly get total time passed between two dates such as:

  • How many days or years (divide by 365) an employee has been on staff
  • How much time passed between ticket creation and resolution
  • How much time was spent traveling

Whereas NETWORKDAYS() can be used to get metrics such as:

  • How many business days were spent on a project
  • How many week days are available for allotment to a future project
  • How many school days are in the year

The structure of the formulas and their arguments are as follows:

DAYS() = end date, start date (i.e. =DAYS(End, Start) -or- =DAYS(D2,C2))

NETWORKDAYS() = start date, end date, holidays to exclude (i.e. =NETWORKDAYS(Start, End, Sheet2!B2:B8) -or- =NETWORKDAYS(C2,D2,Table1[Date]))

NETWORKDAYS() formula in Excel contains arguments for start date, end date, and holidays to exclude.

I’ve created an example workbook that demonstrates both of these formulas in action. You can download the example here.

Excel DAYS and NETWORKDAYS ExampleDownload

Note: For NETWORKDAYS, I placed the holidays table on a separate sheet named Holidays. This allows me to add and remove rows easily for more or fewer holidays without needing to adjust my formulas. You could, alternatively, just use a range of dates (i.e. Holidays!B2:B8) whether or not it’s part of a table but that could result in incorrect information if you don’t also update your formulas to include additional holiday rows when added.

Get business days between dates in Excel with modified weekend parameters

If your weekends aren’t Saturday/Sunday, or you just want to exclude certain days of the week from your calculations, you can alternatively use NETWORKDAYS.INTL instead of NETWORKDAYS. This adds an additional argument after end date and before holidays that allows you specify which day(s) to exclude as the weekends.

NETWORKDAYS.INTL() is structured as follows: start date, end date, weekends, holidays. The following screenshot shows the options available for the weekend argument:

NETWORKDAYS.INTL() includes an argument for weekend specification.

Posted on

Solution: You can’t share this folder because there are too many items in the folder

If you’ve run into either of the following error messages, you may be attempting to share too large a folder, or share within a high-traffic list or library that has too many files or items already shared individually:

  • “You can’t share this folder because there are too many items in the folder.”
  • “You can’t share this item because too many items have already been shared in this library.”

If you want to share a folder, item, or file, there’s a limit of 50,000 uniquely shared items per list or library. So between you and your colleagues, if you’ve shared 50,000 items, files, and/or folders individually (one-off shares using the share button), you’ll need to do some sharing cleanup (remove links and access no longer needed) and consider creating additional lists/libraries or establishing improved best practices for more efficient sharing.

Additionally, the list, library, or folder for which you’re trying to initiate a new share can only have up to 100,000 items total (regardless of share count) before breaking and re-inheriting permissions is allowed. So, for example, if you want to break inheritance on a folder that has 150,000 files already, you’ll need to temporarily reduce the file count to 100,000 or fewer so you can break inheritance, then move the additional files back into it.

See these references for additional information:

Posted on

SharePoint Online file and item limits: Size, quantity, nested folders, and more

blue parrot

I am often asked in trainings and speaking engagements about limits when storing files and items in SharePoint. Some organizations wanting to switch from shared drives to SharePoint are concerned about how many nested folders they have, while others hear mixed advice as to whether folders should be used at all. In this post, I’ll share some good-to-know facts and frequently asked questions regarding limits in SharePoint Online lists and libraries.

What is my entire site’s maximum allowed storage?

That’s up to your administrators. By default, your site can use as much space as your organization has. Learn more here.

Click to enlarge

How many items can I have in a list?

30 million items

What is the file size limit for a file attached to a list item?

250 MB

What is the file size limit for a file stored in a library?

250 GB (this includes files uploaded via Teams, SharePoint libraries, OneDrive, and Yammer)

How many documents can I have in a library?

30 million files and folders

How many levels deep can I go with nested folders?

There’s no known limit to the number of folders (though you may read otherwise if you’re looking at older, outdated guidance on the subject since this has changed over time). It used to be you could only go so far before your URL became too long to function correctly. But since SharePoint uses relative IDs now instead of absolute URLs, you can nest as far as you need to. Best practice would be to minimize your levels of nesting, of course, and to use metadata/columns instead of folders for fluid organization.

I recently wanted to test this answer (Dec 2021) and easily nested 30 levels of folders without a problem. The content in those folders is also still searchable (which you may also hear otherwise in outdated material and guidance).

Should I use folders at all?

There’s no one-size-fits-all approach to document storage and organization. Best practice, though, leans towards highest flexibility which we get from using metadata/column values in library views instead of folder structures. Folders limit us to a single organization structure known best to its creator, whereas with library views, you can have many different views of the same information that suit different users better (i.e. a manager vs employee view, or views filtered to specific time periods or geolocations).

Are there blocked file types?

Recent improvements to SharePoint Online have made it so that there are no known file type limitations in SharePoint. If you’re using SharePoint 2016 or earlier, though, check out this resource for more information.

Are there any character restrictions for file and folder names?

Your file and folder names cannot contain leading or trailing spaces, or any of the following characters: ” * : < > ? / \ |

Why isn’t my large file showing up in SharePoint search results?

SharePoint only accesses the first 150 MB of a document’s metadata and contents for the search crawl service that presents results to you in search. If you have a large file, your search query may be looking for content beyond the initial 150 MB.

SharePoint also stops parsing an item’s contents after 2 million characters. So even if your file is less than 150 MB, it may have too many characters to be crawled. It can still be stored and accessed in SharePoint, just not searchable beyond the first 2 million characters.

How many files with unique permissions can I share?

You can share up to 50,000 files with unique permissions per folder. Once (if) you reach 50,000 in a single folder shared with unique permissions, create a second folder parallel or higher in structure/placement to the original folder (not nested within it) or reconsider your sharing approach to see if there’s a more efficient way to do it with standardized permissions.

What other questions do you have regarding SharePoint limits?

References

Find even more information regarding limits in SharePoint Online at the following links where I verified much of this post’s material:

Posted on

My OneDrive, SharePoint, and Teams online training course is now available as a YouTube playlist

This year I released my free Collaboration in Microsoft 365 training course that covers the essentials of OneDrive, SharePoint, and Teams – the collaboration core of M365. I’ve also just made it available as a YouTube playlist to make sure the content is available to the broadest audience and doesn’t require enrollment or prerequisites.

So when should you use which?

The course itself has more than just the video lessons. I’d recommend enrolling in the actual course for all 30+ video lessons as well as discussion and Q&A forums with me and the other students, quizzes to check your learning along the way, and a completion certificate. It’s also a fun thing for teams to enroll in and go through together. And even if you just do the 2-week free access to knock out the quizzes and get the certificate, you’ll have access to the videos on YouTube forever.

You can also just go through the YouTube playlist (4+ hours total) and use the individual videos to:

  • Skip to and learn topics you’re less familiar with
  • Share important topics and tips with coworkers easily
  • Embed specific videos for your LMS, Teams, SharePoint, or other internal training resources
  • Enhance your presentations and training efforts with in-the-moment learning videos, or as a reference to share after an engagement for continued learning or refreshers

No matter how you choose to learn and share Collaboration in Microsoft 365, I hope you find it valuable and enjoy the experience as much as I enjoyed putting it together.