Activity by Day in Month

[Colmans]

Hi

I want to run some analysis based on data where I can count records based on what day of the month the event occured. This is so I can see patterns of workload across an average month using several years data. How can I do this using the countif function (or alternative)?

Thanks in advance for feedback.

 

[shift-del]

Hi

Insert a pivot table. Date field in rows area and group by day. Date field in values area, aggregate by count.

 

[Toadstool]

Hi Colmans,

Like this?

Colmans.xlsx
	A	B	C	D	E	F	G	H	I
1	Date
2	05-Jul-18		Day	1	2	3	4	5	6
3	03-Jul-19		Count	3	1	3	0	2	0
4	05-Oct-19
5	01-Jun-21
6	01-Jul-21
7	01-Oct-22
8	02-Mar-21
9	03-Jun-19
10	03-Jun-21
11
Sheet1

Cell Formulas
Range	Formula
D3:I3	D3	=SUM(IF(DAY($A$2:$A$99999)=D$2,1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Colmans]

Hi Colmans,

Like this?

Colmans.xlsx
	A	B	C	D	E	F	G	H	I
1	Date
2	05-Jul-18		Day	1	2	3	4	5	6
3	03-Jul-19		Count	3	1	3	0	2	0
4	05-Oct-19
5	01-Jun-21
6	01-Jul-21
7	01-Oct-22
8	02-Mar-21
9	03-Jun-19
10	03-Jun-21
11
Sheet1

Cell Formulas
Range	Formula
D3:I3	D3	=SUM(IF(DAY($A$2:$A$99999)=D$2,1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes, but it doesn't work if I put the dates in dd/mm/yyyy format. I've also changed the data range to a named range but that shouldn't make a difference i'd have thought??

 

[Toadstool]

The format won't change the content. Excel holds dates as number of days since 1 Jan 1900 so if I format my dates column as General it looks like this and the formula still works:

If you format your dates as General and they still look like dates then they must be text data (in which my formula would return a #VALUE! error). There are a couple of ways around that but the best approach would be actually use a date, not text.

 

[Colmans]

The format won't change the content. Excel holds dates as number of days since 1 Jan 1900 so if I format my dates column as General it looks like this and the formula still works:
View attachment 54260

If you format your dates as General and they still look like dates then they must be text data (in which my formula would return a #VALUE! error). There are a couple of ways around that but the best approach would be actually use a date, not text.

it actually gives me a zero value, no error message. I have the data in separate worksheet and that shouldn't matter either I think which is why i've used a named data range. I've formatted the dates same as the original suggestion

 

[Toadstool]

Don't forget that important note under the formula:
"Press CTRL+SHIFT+ENTER to enter array formulas"

 

[Colmans]

Hi

Insert a pivot table. Date field in rows area and group by day. Date field in values area, aggregate by count.

How do you group by day? It seems to default to month and I cant work out how to change that?

 

[Colmans]

Don't forget that important note under the formula:
"Press CTRL+SHIFT+ENTER to enter array formulas"

OK - Sorted thx for help

 

[shift-del]

How do you group by day?

how to group dates in pivot table - Google Search