Countif based on workweek

[SplatP1]

Hello,
I need a formula to return the countif based on the current work week (starting on a Monday).
My data looks like this:

Weekly Activity <tbody> </tbody>	Total Activity <tbody> </tbody>	Date <tbody> </tbody>	03.08.2018 23:00:00 <tbody> </tbody>	03.08.2018 15:00:00 <tbody> </tbody>	02.08.2018 17:00:00 <tbody> </tbody>	01.08.2018 13:00:00 <tbody> </tbody>	01.08.2018 21:00:00 <tbody> </tbody>	26.07.2018 01:00:00 <tbody> </tbody>	26.07.2018 23:00:00 <tbody> </tbody>	20.07.2018 14:00:00 <tbody> </tbody>
3	6				Y	Y	Y	Y	Y	Y
3	6		Y	Y		Y		Y	Y	Y
2	5			Y			Y		Y	Y

<tbody>
</tbody>

The formula in B1 must check if a date in A4:A11 is in the current work week and if it is, then display the count of "Y" in the corresponding columns.
In this case, there are 3 "Y" in the current work week (30.07 - 05.08) in row B, therefore B1 is 3.

Thanks in advance!

Owen

 

[JustynaMK]

Hi Owen,

Not sure if I understood your request correctly, as you wrote that dates are present in cells A4:A11, while your table presents all dates in one row (row 1?). You also wrote that you would like to analyze the current work week (Monday to Friday), while later you wrote that the current work week runs from 30.07 till 05.08 (until Sunday). So apologize in advance if my answer won't be sufficient; but I hope it will be a good starting point for you!

Assuming that your table is present in cells A1:K4 (where cells D1:K1 present dates - make sure that they are formatted as Dates), you can do the following:

Cell B6 -> today's date -> =TODAY()
Cell B7 -> date of last Monday -> =IF(WEEKDAY(B6,2)=1,B6,B6-WEEKDAY(B6-2))
Cell B8 -> date of next Sunday -> =IF(WEEKDAY(B6,2)=7,B6,B6+(7-WEEKDAY(B6,2)))

Now coming back to your table ("Weekly Activity"), you can paste the following into cell B2:
=COUNTIFS(D2:K2,"Y",$D$1:$K$1,">="&$B$7,$D$1:$K$1,"<="&$B$8)

It counts how many "Y" appeared in row 2, where dates in row 1 were greater or equal than last Monday (B7) and smaller or equal than next Sunday (B8). Now you can drag & drop this formula for the remaining rows.

Good luck & best regards,
Justyna

 

[SplatP1]

Hello Justyna,

Thank you. Works perfectly, with a good explanation too.

Much appreciated!

Best Regards
Owen

 

[JustynaMK]

Awesome, glad to hear it works!

Take care,
Justyna

 

[SplatP1]

Hello Again Justyna!

Your weekly formula works perfectly and really helped me out, but I need to ask if you know how to extend it for the current month as well? i.e. count all "Y" that are in the current calendar month.

Hoping you do

Best Regards
Owen

 

[SplatP1]

To make the above a little clearer: I need a new, separate formula to be put in a new cell, not an extension of the previous formula.

 

[JustynaMK]

Hi Owen,

Sure - should be quite easy to implement.

Remember when we used =TODAY() formula in cell B6, and then constructed our "last Monday" and "next Sunday" dates in cells B7 and B8?
Now we can do the following:
Cell B6 -> today's date -> =TODAY()
Cell B7 -> 1st of the current month -> =DATE(YEAR(B6),MONTH(B6),1)
Cell B8 -> last day of the current month (+ time value) -> =EOMONTH(B7,0)+TIME(23,59,59)

That should be it. You can still use the same formula as we used for "Weekly Activity" column counter.

Let me know if that helps!

 

[SplatP1]

Hi JustynaMK,

It sure does! Just like the first one, perfect!

Thanks again!

Cheers.
Owen

 

[JustynaMK]

cool news.

Take care!