Pivot Table with merged date fields

Question

I have a source data sheet, each data item having two date fields, startDate and endDate. What I would like to to in excel is generate a pivot table with row headers for each date from either of these columns, and two summary columns, one for Count Started, the other Count Ended.

For example, the following source data:

ItemId  |  startDate  |  endDate

1       |  6/1/16     |  6/2/16

2       |  6/2/16     |  6/3/16

3       |  6/1/16     |  6/3/16

Would produce a pivot table like this:

Date    |  Started    |  Ended

6/1/16  |  2          |  0

6/2/16  |  1          |  1

6/3/16  |  0          |  2

Community · Accepted Answer · 2017-05-23 12:17:44Z

0

I doubt I would choose a PivotTable solution for this (that's unlike me!) but I think possible with a PT:

1) Create a PT from multiple consolidation ranges (example here) with ranges A:B and A:C (assuming ItemID is in A1).

2) After 7. select ColumnsA:C (in the new sheet) and apply Remove Duplicates (with all Columns checked).

3) Create a new PT from what remains (Column for COLUMNS, Value for ROWS, Count of Row for VALUES)

4) Right-click on startDate, Move, and click on first option.

5) In PivotTable Options..., Totals & Filters uncheck both Grand Totals and in Layout & Format, Format, check For empty cells show and enter 0.

6) Adjust labels to suit.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 23, 2017 at 3:44

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pivot Table with merged date fields

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related