+ Reply to Thread
Results 1 to 4 of 4

Number of days (mon,tue&thur) between today and deadline

  1. #1
    Registered User
    Join Date
    03-24-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    1

    Number of days (mon,tue&thur) between today and deadline

    Hey all - I'm a Newbie

    I have a deadline to meet for school, and to make it easier for me, i have used the following to calculate the number of days between today and the deadline

    Cell J17:
    Please Login or Register  to view this content.
    Cell M17:
    Please Login or Register  to view this content.
    (Deadline)

    Cell M19:
    Please Login or Register  to view this content.
    (Number of days left)

    Now i also want to calculate the number of school days, i have school on Mon, Tue and Thur (3 days a week). I need help with a formula that checks how many school days are between today and deadline.

    Any help will be appreciated,

    Thank you

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Number of days (mon,tue&thur) between today and deadline

    Extract from Chip Perason's site

    The following formula does everything that the traditional NETWORKDAYS function does, plus it allows you to select as many days of the week as you want to exclude from the calculations. There are two flavors of the formula. The first version does not allow a list of holidays to exclude from the count. The second version does allow a list of holidays to exclude. Both formulas require a range named ExcludeDaysOfWeek that lists the day of week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the calculation.

    Formula Without Holidays

    =SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),1,0))

    In this formula, StartDate is the data at which counting will begin. EndDate is the last date of the period to count. ExcludeDaysOfWeek is a range of up to 7 cells indicating the day-of-week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the count. You may, if you choose to, replace the range reference of ExcludeDaysOfWeek to a hard-coded list of day numbers. For example,

    =SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),1,0))

    Note that the days of the week are enclosed in curly braces { }, not parentheses.

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Number of days (mon,tue&thur) between today and deadline

    Forgot to mention that you need to install the analysis Tool Pack for it to work

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Number of days (mon,tue&thur) between today and deadline

    This formula will count Mons, Tues and Thurs between start date and end date (inclusive)

    =SUM(INT((WEEKDAY(J17-{2,3,5})+M17-J17)/7))

+ 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