Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Number of occurences by date

Posted on 2014-03-13
Medium Priority
149 Views
Hi All,

I have spreadsheet in column "A" I have a date/time stamp formatted like below,

1/13/14 12:00 AM

I need to count the number of occurrences for each day,

Thanks,
0
Question by:J_Drake
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 39

Accepted Solution

nutsch earned 536 total points
ID: 39928337
The easiest way is to select your data, insert pivot table, put the day as a row field, and as a data fields, then right click the row field and Group by day. This will give you a table with a count of occurrences by day.

Thomas
0

LVL 39

Expert Comment

ID: 39928339
If you don't have different times and only days, you can skip the grouping part.
0

LVL 19

Assisted Solution

regmigrant earned 532 total points
ID: 39928380
this formula will count only the day elements
=COUNTIFS(\$A\$1:\$A\$36,">="&INT(A1),\$A\$1:\$A\$36,"<"&INT(A1+1))

but it will repeat the count for each date entry
0

LVL 33

Assisted Solution

Rob Henson earned 532 total points
ID: 39928768
Assuming list of dates and times in column A andseparate list of individual dates in column D:

=COUNTIFS(\$A\$3:\$A\$37,"<"&\$D3+1,\$A\$3:\$A\$37,">="&\$D3)

D3 being day to be counted.

Counts entries where value is less than next day and greater than or equal to current day. Time in excel is recognised as a decimal part of a day so does same as INT part of regmigrant's suggestion but puts date in separate column so no repeats.

Alternative, get round repeats using an IF statement comparing the date parts for movement to next day:

=IF(INT(\$A4)=INT(\$A3),"",COUNTIFS(\$A\$1:\$A\$38,">="&INT(A3),\$A\$1:\$A\$38,"<"&INT(A3+1)))

Puts count against last entry of each day.

Thanks
Rob H
0

LVL 49

Expert Comment

ID: 40022142
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month5 days, 13 hours left to enroll