Adding a start and finish date to a function counting no. of specific days in month

Please also refer to thread "Quick date calculation functions"

--------------------------------------------------------------------------------
I am using the below function from this thread:

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+2))+35)>7)

which will give the number of Tuesdays in a month.

For a different weekday, change the '2' near the end accordingly:

1:Monday 7:Sunday etc etc

Can anybody please assist on how I can include a start and end date for this function.
ie I have set up a count of each specific day per month for Feb2010 to December 2011.
How do I then implement a specific date into this eg I only want the count of each specific day in each month starting at the 13th Feb, and ending at 22nd December.

Also, I have just joined with '+ signs' 7 of the above functions for days 1 through to 7, creating a formula that takes up 5 lines in the formula bar. Is there a basic way for me to do a "For days 1:7, do (xxxxxxxxxxx) "Above formula"

Thanks
Darren

you may want to provide a link to the thread you're referring to, so people can actually read up on it.

Also, I have just joined with '+ signs' 7 of the above functions for days 1 through to 7, creating a formula that takes up 5 lines in the formula bar. Is there a basic way for me to do a "For days 1:7, do (xxxxxxxxxxx) "Above formula"

the formula calculates the number of specific week days in a given month. If you add up the results for each weekday, you'll effectively arrive at the total number of days in that month, no? What do you want to achieve by adding the results?

As Teylyn states - if it's your desire to simply calculate total days in a month from a given start point within that month (eg 13-Feb) then:

=1+DATE(YEAR(A1),MONTH(A1)+1,0)-A1
format as General

will give you days in the month specified by date in A1 - eg 13-Feb = 16

If you have the Analysis ToolPak activated (Tools -> Addins) you can simplify the above using EOMONTH, eg:

=1+EOMONTH(A1,0)-A1

The numbers in your sample file are a little curious... I'm guessing these are wrong ? (ie WIP)

My suggestion would be thus:

Please Login or Register  to view this content.

The above will ensure the starting month is the first date used in your data set.

Further to the above - to aid latter calcs - I'd store the end date of each month in the row above, eg:

Please Login or Register  to view this content.

With the above in place we can then populate the Matrix based on start date, end date & weekday allocation, eg:

Please Login or Register  to view this content.

Using your sample data this would generate a total of 1212 days
(10 in Oct-10, 247 in Nov-10, 253 in Dec-10, 258 in Jan-11, 232 in Feb-11, 212 in Mar-11)

Given use of 2003 in all of the above I've avoided use of ATP functions like EDATE, EOMONTH etc and have used DATE based functions instead.

I hope that helps

P.S. I'm pretty sure Daddylonglegs could condense the above - it's not overly efficient given use of SUMPRODUCT - however given the ranges used are small (max 31 days) I hope it won't prove too slow.

Regards Seat Multiplier - I confess I didn't make it to the end of your existing formulae so didn't see the *$C$1

The formulae suggested should return for you the total frequency for a given flight for a given month based on the days denoted as "flying days" - adjust resulting value via additional multiplier as nec.

Post back if you have any further issues.