Solved

subtotal dates/times

Posted on 2014-03-03
10
147 Views
Last Modified: 2014-03-03
Hello again, Experts, I need your help.

See attached example.  Column A contains date/time entries (1/6/2014  7:38:52 PM).  I want to sub-total them by how many entries there are for each hour segment.  For example:
1/2/2014 5pm = 3
1/3/2014 5pm = 4
1/6/2014 5pm=  3
1/6/2014 6pm = 1
1/6/2014 7pm = 1

Thanks,
Gary (frigid still in Cincinnati)
N-Kansas-City-60-day-Study-EE-Su.xlsx
0
Comment
Question by:garyrobbins
  • 6
  • 4
10 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39901416
First place a space before PM in your entries in column B, then try:

=COUNTIFS(A:A,">="&B2,A:A,"<"&B2+TIME(1,0,0))

copied down.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39901431
You can also summarize with a Pivot Table.

Select the column.. go to Insert Pivot Table, drag the item to the Row Label area, then drag again to the Summation area.

You can then right click the row label column and select Group, then select Dates and Hours (you can adjust Start/End times)
0
 

Author Comment

by:garyrobbins
ID: 39901467
where is the "Summation area"?
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:garyrobbins
ID: 39901474
=COUNTIFS(A:A,">="&B2,A:A,"<"&B2+TIME(1,0,0))

Have you tested this formula in Col B of my sample file?
I get a circular reference warning.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39901482
Its the section to the right of the row label area (Sigma Values)
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39901488
The formula goes in column C.  It references your column B inputs against the column A data.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39901498
again, the summation area is the area of (S Values)


... can't enter Sigma symbol
0
 

Author Comment

by:garyrobbins
ID: 39901557
Ok, so I am a newbie to Pivot Tables...

I got the Summation area drop...
Col B now shows "Count of Start Date/Time"

[ right click the row label column and select Group, then select Dates and Hours ]

When I "right click col A" I don't see a "select group" option.
What to do?
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39901571
In the actual Pivot table that was created, right click in the left column, and you should see a Group category.  Select that.  You may see that "Months" is preselected.  Deselect that first.  Then select Hours and select Days.

At the top check the Starting At checkbox and adjust the time so that it has 17:00:00 as the time (assuming 5:00 PM is your smallest time entry).

Click Ok to finish.

You can then go to the Design tab in the PivotTable Tools section of the Ribbon that is active when you are in the Pivot table.  From the Report Layout section, select Show in Tabular Form (you might like that format better).
0
 

Author Comment

by:garyrobbins
ID: 39901643
Ok, that was fun and easy.  Thank you.  You guys are terrific!

A special thanks for the detailed description of steps.

Gary
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question