# number of days between dates by week ending date

1. ## 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. ## Re: number of days between dates by week ending date

Hi,

Formula:
`Please Login or Register  to view this content.`

Use that in E5 and drag it to fill the area

3. ## 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. ## Re: number of days between dates by week ending date

Originally Posted by seawcbee
Hi,

Formula:
`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. ## Re: number of days between dates by week ending date

Originally Posted by bebo021999
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. ## Re: number of days between dates by week ending date

Originally Posted by bebo021999
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. ## 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. ## 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. ## 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.

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

#### 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