Excel 2010-2019 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

Number of Work Days
Use NETWORKDAYS to count the number of work days between two dates
Create a custom function in VBA to handle odd situations

 Q: I need to calculate the number of work days between two dates. I understand the NETWORKDAYS function allows me to calculate Monday thru Friday, but we also work on Saturdays. How can I count the number of days between two dates, but exclude all of the Sundays only? A: Very good question. Yes, you can use NETWORKDAYS if you only need to worry about excluding Saturdays, Sundays, and a list of holidays. However, if you have a custom requirement like yours, you'll need to write your own function using VBA. Here is the function I wrote. Note that in the video, the end of the first line of code says "as Date" but it should be "as Long". It works fine in both cases, but you should return a Long instead of a Date value. Public Function MyWorkDays(D1 As Date, D2 As Date) As Long   DayCount = 0   For X = D1 To D2     D = Weekday(X) '1=sun, 7=sat     If D <> 1 Then DayCount = DayCount + 1   Next X   MyWorkDays = DayCount End Function Here is a FREE video tutorial that shows you how to set up this function step-by-step:

By Richard Rost

 Check out these other pages that may be of interest to you:
 2/3/2023 TextSplit 1/27/2023 Math in Fields 12/12/2022 Tax Brackets 12/9/2022 Double Lookup 10/24/2022 Follow Hyperlink 9/17/2022 Very Hidden 8/11/2022 Crosstab 8/10/2022 Transpose 7/26/2022 Lottery Numbers 2 7/22/2022 Lottery Numbers