How to count number of timestamp entries per day

Question

what formula could I use to show the average number of timestamp entries per day? I use a Google Form that enters data into a Google Sheet. I want to eventually create Charts showing the average number of timestamps per day/week/month, with trends. (For example, we receive 5 telephone calls per day on average). I'm thinking I first need a formula to count the number of timestamp entries per day, then I can use another formula to find the average per day/week/month, etc.

Below is an example of the timestamp entries all in Column A now showing only the date as I removed the "time" using =ArrayFormula(Responses!A:A)

8/8/2016
8/8/2016
8/9/2016
8/9/2016
8/9/2016
8/9/2016
8/9/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/10/2016
8/11/2016
8/11/2016

Tom Sharpe Tom Sharpe · Accepted Answer · 2016-09-15T11:49:52

You can do the average per day using built-in Google Sheets formulae:-

Count(A2:A100)/CountUnique(A2:A100)

if the time stamps are in column A.

You could do the average of all of them per week just by taking the difference between the last and the first and dividing by 7:-

=COUNT(A2:A100)/((index(A2:A100,count(A2:A100))-A2+1)/7)

and you could do a similar thing for months (a bit more difficult because of the variable length of each month).

But I think if you are going to do trend lines, you want to get the unique days, weeks and months as you suggest and then do the counts.

So it's just

unique(a2:a100)

in (say) column B to get the separate days

=unique(filter(A2:A100,weekday(A2:A100)=2))

in (say) column D to get the beginning of each separate week and

=unique(filter(A2:A100,day(A2:A100)=1))

in (say) column F to get the beginning of each month.

Then you could set up countifs to count them:-

=countif(A2:A100,B2)

in C2

=countifs(A2:A100,">="&D2,A2:A100,"<"&(D2+7))

in E2

=countifs(A2:A100,">="&F2,A2:A100,"<="&eomonth(F2,0))

in G2

If your days contained still contained different times, would need to use int function to convert to 0:00 midnight, then would need to update above formulae to refer to column B:-

How to count number of timestamp entries per day

2 Answers