• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

subtotal dates/times

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
garyrobbins
Asked:
garyrobbins
  • 6
  • 4
1 Solution
 
NBVCCommented:
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
 
NBVCCommented:
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
 
garyrobbinsAuthor Commented:
where is the "Summation area"?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
garyrobbinsAuthor Commented:
=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
 
NBVCCommented:
Its the section to the right of the row label area (Sigma Values)
0
 
NBVCCommented:
The formula goes in column C.  It references your column B inputs against the column A data.
0
 
NBVCCommented:
again, the summation area is the area of (S Values)


... can't enter Sigma symbol
0
 
garyrobbinsAuthor Commented:
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
 
NBVCCommented:
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
 
garyrobbinsAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with 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.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now