count tasks "on fly" per work week.

Peter Chermensky

Hello Gentlemans ,

Do someone can give direction on how to count tasks "on fly" per work week.

Example;

task start 28.07.20 finish 28.08.20, this task will include in count WW31,32,33 and 34 till its finished.

task start 10.08.20 finish 20.08.20.

ww31 ww32 ww 33 ww34

1任务2的任务2的任务1

Paul Newcome

How are you determining what your work weeks are? Are they based on how many weeks in the year so that 1.1.20 is week 1 and 31.12.20 is week 52 (or possibly week 53)?

Peter Chermensky

Hi Paul,

1.1.20 is week 1 and 31.12.20 is week 52.

Paul Newcome

Ok. Let's try something like this...

=COUNTIFS([Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 55) <= 31, [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) >= 31)

You would just change the 31 to 32 or 33 or 34, etc per each column.

If you did not want to have to update the formula for each column, you could insert a "helper row" and enter the week numbers in that row for each column and then use a cell reference. In the below example I used row 1, but you can use whatever row you want.

=COUNTIFS([Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 55) <= [ww31]$1, [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) >= [ww31]$1)

This will allow you to dragfill the formula across your columns for the different weeks as opposed to having to edit the formula for each of the weeks.