Count days of a range within a weeknum

[HeiNeken]

Hi all,

I have a bit trouble wrapping my head around this little roadblock I came across. I have a cell with a start date and another cell with an end date let's call them a1 and a2. I want to count how many of the days between the 2 dates are the same week number.

example:

a1= 4/11/18
a2= 4/17/18
the total workdays between the 2 dates are 5. 3 of them are weeknum 15 and 2 are weeknum 16

I am looking for a way to count the number of days for each weeknum.

thank you in advance
Dan

 

[HeiNeken]

[/URL][/IMG]
here is an image of my spreadsheet that I am working on. I hope that explains better what I am trying to do.

thank you for any help

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]

4/11/2018​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

4/17/2018​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

15​

[/td][td]

2​

[/td][/tr]
[tr][td]

5​

[/td][td]

15​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

16​

[/td][td]

3​

[/td][/tr]
[tr][td]

7​

[/td][td]

16​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

16​

[/td][td][/td][/tr]
[/table]

In A4 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(ISNA(MATCH(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2))),{6,7},0)),WEEKNUM(ROW(INDIRECT($A$1&":"&$A$2))),""),ROWS($A$4:A4)),"")

In B4 just enter and copy down:

=IF(COUNTIFS($A$4:A4,A4)=1,COUNTIFS($A$4:$A$8,$A4),"")

Hope this is of any use for your great image.

 

[Ingolf]

@Aladin,
If data are like below, what formula is necessary
In A2 is start date, in B2 End Date, in D1 is (week)15, in E1 is (week)15 and so on...
In D2:G2 need formula. Same problem like before but data are diferent location. Thank you.
[TABLE="width: 427"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD="align: right"]11.04.2018
[/TD]
[TD="align: right"].....17.04.2018.....
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@Aladin,
If data are like below, what formula is necessary
In A2 is start date, in B2 End Date, in D1 is (week)15, in E1 is (week)15 and so on...
In D2:G2 need formula. Same problem like before but data are diferent location. Thank you.
[TABLE="width: 427"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD="align: right"]11.04.2018
[/TD]
[TD="align: right"].....17.04.2018.....
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is D1:G1 given? If so:

In D1 control+shift+enter, not just enter, and copy across:

=SUM(IF(ISNUMBER(MATCH(IF(ISNA(MATCH(WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2))),{6,7},0)),WEEKNUM(ROW(INDIRECT($A$2&":"&$B$2)))),D$1,0)),1))

 

[HeiNeken]

Is D1:G1 given? If so:

In D1 control+shift+enter, not just enter, and copy across:

=SUM(IF(ISNUMBER(MATCH(IF(ISNA(MATCH(WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2))),{6,7},0)),WEEKNUM(ROW(INDIRECT($A$2&":"&$B$2)))),D$1,0)),1))

First off thank you both very much for your help. this formula did the trick for the most part . it sems that the week with the days Thu & Fri is short 1 and the next week with mon, tue, wed has 4 as instead to 3. (See the picture) I was wondering, how could I filter out Saturdays and Sundays.

[/URL][/IMG]


thank you
Dan

 

[HeiNeken]

Hi,

so after playing with the formula, I was able to figure out how to adjust for Sunday week start. Now it works great.

again thank you all very much for helping in this matter.

have a great Day
Dan

 

[Aladin Akyurek]

Hi,

so after playing with the formula, I was able to figure out how to adjust for Sunday week start. Now it works great.

again thank you all very much for helping in this matter.

have a great Day
Dan

You are welcome. Thanks for the update.

 

[Ingolf]

Is D1:G1 given? If so:

In D1 control+shift+enter, not just enter, and copy across:

=SUM(IF(ISNUMBER(MATCH(IF(ISNA(MATCH(WEEKDAY(ROW(INDIRECT($A$2&":"&$B$2))),{6,7},0)),WEEKNUM(ROW(INDIRECT($A$2&":"&$B$2)))),D$1,0)),1))

As always, the best. Thank you Aladin.

 

[HeiNeken]

Hi gents,

Well, and I am back with another question. I came across another problem I hope you could help me with. I tried to adjust the formula you gave me but it got me nowhere. What happened is, that the last weeks week number of April is the same week number as for the first week of May. Well, that messes with my monthly total. It would show me the total of the week in both months. the reason why it shows zero currently at the last week of April is cause I added a formula to hide the week with the fewer days so the yearly total wouldn't be messed up.

Could you maybe adjust the formula so that it would split the week so, that one part is shown in April and the rest in May? I made a picture to better explain what I mean.

[/URL][/IMG]

Thank you all in advance
Dan