Countif based on workweek

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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>
36YYYYYY
36YYYYYY
25YYYY

<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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
654
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jun 15, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Hello Justyna,

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

Much appreciated!

Best Regards
Owen
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
654
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Awesome, glad to hear it works!

Take care,
Justyna
 

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 15, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 28, 2016
Messages
654
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jun 15, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Hi JustynaMK,

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

Thanks again!

Cheers.
Owen
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top