+ Reply to Thread
Results 1 to 9 of 9

number of days between dates by week ending date

  1. #1
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    359

    number of days between dates by week ending date

    Need to show the working days (Mon-Fri) by week ending date (Sunday) in-between two dates

    Anyone able to write a formula in the attached to replace my manual figures in the yellow area

    Days leave by week ending date.xlsx

    Thanks

  2. #2
    Registered User
    Join Date
    10-19-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: number of days between dates by week ending date

    Hi,

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Use that in E5 and drag it to fill the area

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: number of days between dates by week ending date

    Try in E3:

    =COUNT(MATCH(ROW(INDIRECT(E$2-6&":"&E$2-2)),ROW(INDIRECT($A3&":"&$B3)),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down and accross

  4. #4
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    359

    Re: number of days between dates by week ending date

    Quote Originally Posted by seawcbee View Post
    Hi,

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Use that in E5 and drag it to fill the area
    Won't work as does not take into account the start & END dates and weekending

  5. #5
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    359

    Re: number of days between dates by week ending date

    Quote Originally Posted by bebo021999 View Post
    Try in E3:

    =COUNT(MATCH(ROW(INDIRECT(E$2-6&":"&E$2-2)),ROW(INDIRECT($A3&":"&$B3)),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down and accross
    Fantastic, so much simpler than what I was trying to do. Thankyou. Just need to get me head around how that works

  6. #6
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    359

    Re: number of days between dates by week ending date

    Quote Originally Posted by bebo021999 View Post
    Try in E3:

    =COUNT(MATCH(ROW(INDIRECT(E$2-6&":"&E$2-2)),ROW(INDIRECT($A3&":"&$B3)),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Drag down and accross
    any chance you can do a bit of a step by step description of how that works. Thanks

  7. #7
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 2013
    Posts
    359

    Re: number of days between dates by week ending date

    is there anyway I can limit it to purely working days (mon-fri) in a particular month, i.e. Oct only in this example

    e.g. start 28th Sep 15, End 4th Oct. Only want this to show 2 days in Oct for week ending 4th, instead of 5 days as 3 of them relate to sep

    Thanks

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: number of days between dates by week ending date

    With new request, modify as:

    Please Login or Register  to view this content.
    I will come back to go step by step with it soon.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,711

    Re: number of days between dates by week ending date

    Overall view, I try to MATCH every day in list A (days of the ending week (4-Oct) within Oct) WITH list B (list of dates from start to end date).
    COUNT(MATCH(ListA,ListB))
    List B: list of consecutive days from 28/9 to 4/10, equals array {42275,,42281}, equals ROW(42275:42281),equals ROW(INDIRECT($A3&":"&$B3))
    List A: List of consecutive days of the week ending at 4-Oct, but within Oct.
    ===>List of working days of the week: ROW(INDIRECT(E$2-6&":"&E$2-2)), with E2 is last Sunday:{28-sep,29-sep,30-sep,1-Oct,2-Oct}
    ===>List of working days of the week, but within Oct: MONTH(ROW(INDIRECT(E$2-6&":"&E$2-2)))=MONTH(E$2), returns TRUE or FALSE
    List A equals 2 lists of working days multiply together: ROW(INDIRECT(E$2-6&":"&E$2-2))*(MONTH(ROW(INDIRECT(E$2-6&":"&E$2-2)))=MONTH(E$2)),returns date value or 0
    {0,0,0,0,1-Oct,2-Oct}
    MATCH(List A, List B)={N/A,N/A,N/A,N/A,,4,5}
    COUNT(MATCH(ListA,ListB))=2
    Hope it is clear now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] dates ending in next 30 to 60 days
    By phb_abs in forum Excel General
    Replies: 2
    Last Post: 12-26-2014, 09:08 AM
  2. [SOLVED] Find week days within the date range and return number
    By CaineSmith in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-06-2014, 03:47 PM
  3. [SOLVED] Sum 7 days of information based on week ending date
    By tatyanamarie in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-18-2013, 07:22 AM
  4. Sheets for every week + start and ending date of the week
    By wimexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2012, 01:48 PM
  5. [SOLVED] Calculating a week of dates/days based on the beginning date entered...
    By tiffany04530 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-27-2012, 09:55 AM
  6. Replies: 3
    Last Post: 02-23-2010, 03:47 PM
  7. How to format cells to show dates as the week-ending date of that
    By dereksmom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2006, 11:45 AM

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