The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

I have a spreadsheet that calculates the # number of weekdays that a client was enrolled in a program between an two dates. In my example, lets say the billing period is 9/1/13(b26) and 9/30/13(b27). I want to calculate the # of billable days that an individual was enrolled between those two dates. In row 31 you can see john doe enrolled in the program on 8/29/13. His end date is blank which means he is still enrolled. My formula correctly calculates that he should be billed for a total of 18 (e31) days. 18 is the number of days he was enrolled between 9/1 and 9/30 - holidays (named range is bethlehem).

Now to what I cant figure out. I have some clients that are enrolled part time. Lets say they should get billed for every Tuesday and Thursday, or every Mon, Wed, Fri. How can I apply the same formula as above, but instead of calculating all weekdays - holidays, i want to calculate all Tuesdays and Thursdays that a client was enrolled between two billing dates (-holidays).

Thank you very much for your help.

Now to what I cant figure out. I have some clients that are enrolled part time. Lets say they should get billed for every Tuesday and Thursday, or every Mon, Wed, Fri. How can I apply the same formula as above, but instead of calculating all weekdays - holidays, i want to calculate all Tuesdays and Thursdays that a client was enrolled between two billing dates (-holidays).

Thank you very much for your help.

```
=INT((D31-C31)/7)+ABS(WEEKDAY(D31)=G31)-COUNTIFS(Holidays,"=>"&C31,Holidays,"<="&D31,HolidayDays,G31)
```

In order to make it work you need the following support:-1. In G31 you need to specify a weekday, say 3 for Tuesday. This is the first of the values you have in column E.

2. You need a list of the holidays, I believe "Bethlehem" in your workbook. I have called it "Holidays".

3. You need a list of the weekdays of the holidays. In your worksheet you have it in column B. I have named it "HolidayDays"

This will give you the number of billable days for a single weekday. If you have more than one just add the result of this function to another, similar, one calculating it for another weekday. That would require you to have more than one value in G31. The function would become very bloated if you wanted to accommodate such an eventaulity. My suggestion would be to have separate billing references for the various days, resulting in separate rows.

Thanks

Rob H

=arrayformula(sum(int((wee

This works in google docs but can easily be adapted to work in Excel too.....

regards, barry

Your formula looks great! I ran into on problem though. Turns out when I extract the data i am putting into my google spreadsheet, it is formatting the cell that I am looking to use as the holiday reference as Bethlehem Middle (206)

When i try and create named ranges, it tells me i can not have spaces,(, and also cant start with a #. I need a way to extract the 206. I came up with a way to make _206 by using the formula

="_"&Substitute((MID(G1,FI

If I had Bethlehem (201) in G1, it would spit out _201

If i replace all references of b31 in your formula, with the above, i get an error. Below is what my formula looks like now, and is in f31 of my spreadsheet.

=arrayformula(sum(int((wee

Your formula is in f32

I'm at work now and I haven't got a browser that likes googledocs......I'll take a look when I get home in a couple of hours......

regards, barry

Avona Elementry School (152)

Bangor Area Middle School (176)

BF Morey (180)

Then I will create ranges named

_152

_176

_180

It doesn't need to be an underscore, thats just a character i chose that google spreadsheet allowed in a named range.

Thank you for your help!!!

```
=arrayformula(sum(int((weekday(max(C34,B$26)-{2,3,4,5,6})+min(if(D34="",today(),D34),B$27)-max(C34,B$26))/7)*IF(E34="M-F",{1,1,1,1,1},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E34&","))+0)))-arrayformula(sum((indirect("_"&Substitute(MID($B34,FIND("(",$B34)+1,10),")",""))>=max(C34,B$26))*(indirect("_"&Substitute(MID($B34,FIND("(",$B34)+1,10),")",""))<=min(if(D34="",today(),D34),B$27))*(weekday(indirect("_"&Substitute(MID($B34,FIND("(",$B34)+1,10),")","")))=IF(E34="M-F",{2,3,4,5,6},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E34&","))*{2,3,4,5,6}))))
```

="_"&LEFT(RIGHT(B34,4),3)

....and I also realised that you might get negative results in some circumstances which isn't right, so incorporating the above version for the named range and an additional MAX function to prevent negative values gives you this:

=max(0,arrayformula(sum(in

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Apologies, should be this version in F34

=max(0,arrayformula(sum(in

regards, barry