+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    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
    Last edited by DonkeyOte; 03-30-2010 at 02:02 AM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Adding a start and finish date to a function counting no. of specific days in mon

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

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    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?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    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

  5. #5
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    apologies for being unclear. (aka Random)

    I have a data sheet that lists the days a flight travels on a particular route (eg flight A only goes Monday, Thursday, Saturday).

    The reason I need to add the 7 seperate totals is to determine how many Mondays, Tuesdays etc are in the month, and then multiply that by if a flight on that route travels Monday, Tuesday etc. So a total days in the month doesnt assist in that it needs to determine the individual days.
    To clarify, if there are 4 or 5 mondays in a month, or Fridays, or whatever day, will make a difference, as opposed to total business days etc.

    Regarding the start and finish date ..... We will be advised that a particular route will change on a given date (eg up until 10 December it will go Monday, Wednesday, Friday) and from 11 December onwards will change to Tuesday, Thursday, Sunday). So i have built seperate rows for each date group. The columns then are month by month.
    I need the start and finish date included in the formula so that if the route changes on say 10th December, it only counts how many mondays occur up to and including the 10th December, how many tuesdays etc etc).

    Hope this helps ... I have attached a data sheet to give a better indication of what I am looking for.
    Columns W - AE (will be extended across indefinately) have the formula in them I am working on.
    Columns M & N have the start and finish dates of that route.


    On the attached sheet, the day indicators are only representative of each day (monday 1, Tuesday 2 etc) and not a volume of flights. So it is only 1 x the day, not 5 x if on a Friday)
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    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.

  7. #7
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    Thanks DonkeyOte, I will try to put these into the sheet and see how it goes.

    Re: the numbers - the calculation is multiplied by cell C1 = 361, being the number of seats on the aircraft That is the final number i have to come to, how many seats (which in turn means max possible customers). This may explain why they looked a touch higher than expected.

    I also think that this thread has managwed to get a bit off track. May well be due to me being vague at the start. The details I need to work with at the end of all of this is a monthly number of seats flying per individual route, rather than the total monthly figure.
    This is why the count of each monday, Tuesday etc becomes so important.

    Hope this clarifies for all.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    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.

  9. #9
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Re: Adding a start and finish date to a function counting no. of specific days in mon

    Hi all,

    Unable to mark as solved .. I thinik i left it too long.
    Is anyone able to do this for me please?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1