Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate number of tuesdays and thursdays between two dates

Posted on 2013-11-06
15
Medium Priority
?
1,021 Views
Last Modified: 2015-03-02
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.
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
15 Comments
 

Author Comment

by:colonialiu20
ID: 39628181
I've been trying to get this to work all day, but have been unsuccessful.  Im hoping someone could try and tackle this one.
0
 

Author Comment

by:colonialiu20
ID: 39631441
just in case someone missed it, the link to the spreadsheet is here, https://docs.google.com/spreadsheet/ccc?key=0Am5eEooQEL63dG9wT3BhbkZNb1NzX3NRYkR0Q0hIYXc&usp=sharing
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39636324
Please try this formula in F31,
=INT((D31-C31)/7)+ABS(WEEKDAY(D31)=G31)-COUNTIFS(Holidays,"=>"&C31,Holidays,"<="&D31,HolidayDays,G31)

Open in new window

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.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 39639069
Might be worth having a set of columns with one for each day of the week so you can then count all the Mondays, Tuesdays, Wednesdays etc etc. Then just a sum of the group gives total number of days.

Thanks
Rob H
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39640488
This is a long formula for F31 but it should allow you to retain your current setup, i.e. holidays in a range called Bethlehem, column E to contain the days that should be included, either M-F for all weekdays or just weekdays to be included separated by commas, e.g. T,F

=arrayformula(sum(int((weekday(max(C31,B$26)-{2,3,4,5,6})+min(if(D31="",today(),D31),B$27)-max(C31,B$26))/7)*IF(E31="M-F",{1,1,1,1,1},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E31&","))+0)))-arrayformula(sum((indirect(B31)>=max(C31,B$26))*(indirect(B31)<=min(if(D31="",today(),D31),B$27))*(weekday(indirect(B31))=IF(E31="M-F",{2,3,4,5,6},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E31&","))*{2,3,4,5,6}))))

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

regards, barry
0
 

Author Comment

by:colonialiu20
ID: 39641927
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,FIND("(",G1)+1,10)),")","")
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((weekday(max(C31,B$26)-{2,3,4,5,6})+min(if(D31="",today(),D31),B$27)-max(C31,B$26))/7)*IF(E31="M-F",{1,1,1,1,1},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E31&","))+0)))-arrayformula(sum((indirect("_"&Substitute((MID(B31,FIND(“(“,B31)+1,10)),")",""))>=max(C31,B$26))*(indirect("_"&Substitute((MID(B31,FIND(“(“,B31)+1,10)),")",""))<=min(if(D31="",today(),D31),B$27))*(weekday(indirect("_"&Substitute((MID(B31,FIND(“(“,B31)+1,10)),")","")))=IF(E31="M-F",{2,3,4,5,6},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E31&","))*{2,3,4,5,6})))))))

Your formula is in f32
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39642008
Are you saying that all of the "Site" column will have entries with a number in brackets at the end?

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
0
 

Author Comment

by:colonialiu20
ID: 39642026
Thats is correct.  the sites will look like...

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!!!
0
 

Author Comment

by:colonialiu20
ID: 39642298
I think i might have it.  see the formula in f34.  It appears to work/make sense.  Is there a way to make it simpler?

=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}))))

Open in new window

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39642508
That's the simplest I know - if the number in brackets is always 3 digits then you could get the named range with a shorter formula, i.e.

="_"&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(int((weekday(max(C35,B$26)-{2,3,4,5,6})+min(if(D35="",today(),D35),B$27)-max(C35,B$26))/7)*IF(E35="M-F",{1,1,1,1,1},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E35&","))+0)))-arrayformula(sum((indirect("_"&Substitute(MID($B35,FIND("(",$B35)+1,10),")",""))>=max(C35,B$26))*(indirect("_"&Substitute(MID($B35,FIND("(",$B35)+1,10),")",""))<=min(if(D35="",today(),D35),B$27))*(weekday(indirect("_"&Substitute(MID($B35,FIND("(",$B35)+1,10),")","")))=IF(E35="M-F",{2,3,4,5,6},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E35&","))*{2,3,4,5,6})))))

regards, barry
0
 

Author Comment

by:colonialiu20
ID: 39642697
Thanks Barry!  I don't see the left function you referred to in the final code.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 39642765
Heck, where did that go!?

Apologies, should be this version in F34

=max(0,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("_"&LEFT(RIGHT(B34,4),3))>=max(C34,B$26))*(indirect("_"&LEFT(RIGHT(B34,4),3))<=min(if(D34="",today(),D34),B$27))*(weekday(indirect("_"&LEFT(RIGHT(B34,4),3)))=IF(E34="M-F",{2,3,4,5,6},isnumber(search(","&{"M","T","W","TH","F"}&",",","&E34&","))*{2,3,4,5,6})))))

regards, barry
0
 

Author Closing Comment

by:colonialiu20
ID: 39644662
Barry,  Thank you so much for your help!!!!  You are amazing.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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 …

705 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