Solved

calculate weekdays excluding holidays

Posted on 2013-11-05
7
458 Views
Last Modified: 2015-03-02
I have a spreadsheet that contains start dates and end dates for each record.  some end dates are blank if the record is still active.  Im looking for a way to calculate the number of weekdays, minus holidays, a client was enrolled in a class.

For instance, a17 - d17 shows that shows that john was enrolled from 8/29/13 - 10/11/13.  I want to run a report that shows me the total # of workdays (- holidays) that John was enrolled between 9/1/13 and 9/30/13.

In another example, a18 - d18 shows that Jim was enrolled from 8/26/13 and is still enrolled because they do not have an end date.  I want to calculate the number of work days (- holidays) between 9/1/13 and 9/30/13.  You should assume that the client is still enrolled because there is no end date.

you can see that in H11 i used the networkdays function in an attempt to solve my problem, but that didnt work exactly as i needed.

see this spreadsheet.

thank you for your help.
0
Comment
Question by:colonialiu20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39625643
For instance, a17 - d17 shows that shows that john was enrolled from 8/29/13 - 10/11/13.  I want to run a report that shows me the total # of workdays (- holidays) that John was enrolled between 9/1/13 and 9/30/13.

In another example, a18 - d18 shows that Jim was enrolled from 8/26/13 and is still enrolled because they do not have an end date. I want to calculate the number of work days (- holidays) between 9/1/13 and 9/30/13.

There seems to be some missing information here...

If you are basing the 'billable dates' off of set values, you are always going to return the same answer:  18.
0
 

Author Comment

by:colonialiu20
ID: 39625799
Sorry, perhaps those were bad examples

Let's say one person started on 8/26 and ended on 9/15.

Let's say another one started on 9/27 and has no end date.

Could you help calculate the # of billable days.
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39625889
Sorry, I am still missing some information:

Are you only trying to calculate billable days for September (9/1/2013-9/30/2013), or all billable days from enroll date to end date?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:colonialiu20
ID: 39626013
I'd like the billable date range to be a variable.  I'd  like to be able to set the billable start date and end date and have it calculate the # of weekdays someone was enrolled.  So for instance, if someone enrolled on 8/15 and in unenrolled on 9/16, and I wanted to run a report for the month of September, with a start date of 9/1 and an end date of 9/30, I'd want it to calculate that the client was present on 11 weekdays in the month of September.(2,3,4,5,6,9,10,11,12,13,16)
I hope that makes sense.
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39626123
You will need to add IF functions, such as (taken from E31 in attached workbook):

=NETWORKDAYS(IF(C31<=$B$26, $B$26, IF(C31>$B$26, C31)), IF(D31="", $B$27, IF(D31<$B$27, D31, IF(D31>$B$27, $B$27))),INDIRECT(B31))

Open in new window


See the attached workbook.  I made additional values in Cells A30:E33 to represent different possibilities:

Enrolled on 8/26 - End on 9/15 = 9 billable days;
Enrolled on 9/27 - No End (still there) = 2 billable days;
Enrolled on 9/5 - End on 9/18 = 8 billable days.

In your examples, they all equate to 18 days, but the days will recalculate based on the info you provide as variables.  This formula will also provide negative numbers...  If you want to hide those, you can add conditional formatting or change your formula to hide negative values:
(this would go in E17 in your original example and be copied down)
=IF(NETWORKDAYS(IF(C17<=$B$26, $B$26, IF(C17>$B$26, C17, "")), IF(D17="", $B$27, IF(D17<$B$27, D17, IF(D17>$B$27, $B$27, ""))),INDIRECT(B17))<0, "", NETWORKDAYS(IF(C17<=$B$26, $B$26, IF(C17>$B$26, C17, "")), IF(D17="", $B$27, IF(D17<$B$27, D17, IF(D17>$B$27, $B$27, ""))),INDIRECT(B17)))

Open in new window

billabledays--1-.xlsx
0
 

Author Closing Comment

by:colonialiu20
ID: 39627214
This guy is brilliant!!
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39627254
I wouldn't say brilliant by any means, but I am glad I could help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 60
Google photos for iphone 6 66
Need conditional formating when doubles 14 32
Formula fix for vlookup and iserror 3 27
Explore the encryption capabilities built into Google Apps and how these features can help you meet privacy policy and regulatory compliance, but are not a full solution. Understand and compare the most popular email encryption services for Google A…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial demonstrates in Google Analytics how to create a custom report that shows you traffic over time using the month of year dimensions. There are also instructions on how to fix Google's odd month of year formatting, which Microsoft …
This Micro Tutorial will demonstrate the easy use of Gmail embedding images in your email so the recipient of your email can view them in context.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question