LinkBack URL
About LinkBacks
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
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
Hi,
Formula:
Please Login or Register to view this content.
Use that in E5 and drag it to fill the area
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
Quang PT
Won't work as does not take into account the start & END dates and weekendingOriginally Posted by seawcbee
Hi,
Formula:Please Login or Register to view this content.
Use that in E5 and drag it to fill the area
Fantastic, so much simpler than what I was trying to do. Thankyou. Just need to get me head around how that works
any chance you can do a bit of a step by step description of how that works. Thanks
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
With new request, modify as:
I will come back to go step by step with it soon.
Please Login or Register to view this content.
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
Register To Reply
Bookmarks