MS Excel count days less list of named dates

[Allilue]

Hi!
sorry, i saw some old posts on this topic but i cant seem to figure out how to do still...

i'm doing a 'simple' excel file where i have date ranges. so for example, Summer Term runs from May 1 to August 31. I have a formula where I can count the days of the week within this date range. I also have a list of dates where I want deducted from the days. so if I have 35 MONDAYS in the Summer Term, then from the list of dates to exclude, I want to be able to count the MONDAYS from the list.

I'm not doing any code or anything...just formulas.

i hope this is an easy one...!

Thanks,

 

[zelgar]

I'm not exactly following what you're asking.

You can use the NETWORKDAYS function to find the number of workdays between 2 dates and it can subtract holidays or other specified days within the range. (e.g., if your dates for Start Date = cell B3; End Date = cell B4; and Holiday Dates or other Exclusion dates in Range D34, the command would be
= NETWORKDAYS(B3,B4,D34)

Also, there's an error on the maximum number of Mondays. There's a maximum of 18 Mondays (17 full weeks) between 5/1& 8/31; not 35.

 

[Gruuuu]

This sounds like a homework assignment

 

[Deniall]

If it's a homework assignment, Allilue has been a student for a long time, having joined Tek-Tips in September 2000. It sounds to me more like the sort of problem a teacher might have, trying to schedule classes.

 

[Gruuuu]

Fair enough. In that case:

* I made a new structured table (from Insert->Table) in A through J. Column Names are:
Semester, start, end, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Under Semester I put Summer 2012. Under start, the start date. Under end the end date.

I made a structured table in column M (from Insert->Table) and named it Holidays with a column called List
I put a few random dates in there, including some inside and some outside of your range.

In the Semester table, under Sunday, I have this formula, which I then copied through to H2:
[tt]
=SUMPRODUCT(--(TEXT([@start]-1+ROW(OFFSET($B$1,0,0,[@end]-[@start],1)),"dddd")=D$1))-SUMPRODUCT((Holidays

• >=[@start])*(Holidays
  • <=[@end])*(TEXT(Holidays
    • ,"dddd")=D$1))
      [/tt]
      
      * Note: I probably should have asked which version of Excel you have before starting on this. Oh well. This solution works in Excel 2010. If you have a different version let me know.
      
      Also, the file is attached, I guess? This new posting format is interesting...

 

[Gruuuu]

If you download the file, you can see that you can add more semesters easily. It just needs a new start date and end date. I would highly recommend giving your semesters different names, but the formula doesn't require it.

 

[zelgar]

I think I understand the problem a little better.

Everything can be done in one formula, but it can get big depending on how many exclusion days you have:

Start Date: E4
End Date: E5
Days to omit: F1:F5
Day of the week to omit: Monday (WEEKDAY CODE 1)

=+INT((E5-E4)/7)+IF(E18>=WEEKDAY(E5),1,0)-IF(WEEKDAY(F1)=1,1,0)-IF(WEEKDAY(F2)=1,1,0)-IF(WEEKDAY(F3)=1,1,0)-IF(WEEKDAY(F4)=1,1,0)-IF(WEEKDAY(F5)=1,1,0)