Pivot Table Dates

[tstri]

I have a dataset that contains a list of items with a unique id, create date, and close date. How do I get a pivot table to have dates by day in the left most column, a count of records created in that day in the second column, a count of records closed in the third column, and the difference in the third.

Date. Created. Closed. Diff
1/1/2019. 150. 100. 50
1/2/2019. 100. 150. -50

 

[alansidman]

Your sample data (really need to see more data in your sample example) does not represent your explanation. I am confused as you have fields in your explanation that are not shown in your example.

 

[tstri]

Your sample data (really need to see more data in your sample example) does not represent your explanation. I am confused as you have fields in your explanation that are not shown in your example.

My apologies:

My data:

Id. Created. Closed
1. 1/1/2019. 1/3/2019
2. 1/1/2019. 1/3/2019
3. 1/1/2019. 1/1/2019
4. 1/2/2019. 1/2/2019
5. 1/2/2019. 1/5/2019
6. 1/3/2019. 1/3/2019
7. 1/3/2019. 1/5/2019
8. 1/3/2019. 1/5/2019
9. 1/3/2019. 1/5/2019
10. 1/3/2019. 1/5/2019

Desired Result:

Date. Created. Closed. Diff
1/1/2019. 3. 1. 2
1/2/2019. 2. 1. 1
1/3/2019. 5. 3. 2
1/5/2019. 0. 5. -5

 

[alansidman]

Inserting the data into a Pivot Table gets the following results. Then using simple excel subtract one value from the other for a difference

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

Id​

[/td][td]

Created​

[/td][td]

Closed​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

1/1/2019​

[/td][td]

1/3/2019​

[/td][td]

​

[/td][td]

Created​

[/td][td]

Closed​

[/td][td]

Count of Created​

[/td][td]

Count of Closed​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td]

1/1/2019​

[/td][td]

1/3/2019​

[/td][td]

​

[/td][td]

1/1/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

4​

[/td][td]

3​

[/td][td]

1/1/2019​

[/td][td]

1/1/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

1/1/2019​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]

4​

[/td][td]

1/2/2019​

[/td][td]

1/2/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

1/3/2019​

[/td][td]

2​

[/td][td]

2​

[/td][/tr]


[tr][td]

6​

[/td][td]

5​

[/td][td]

1/2/2019​

[/td][td]

1/5/2019​

[/td][td]

​

[/td][td]

1/2/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

7​

[/td][td]

6​

[/td][td]

1/3/2019​

[/td][td]

1/3/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

1/2/2019​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr][td]

8​

[/td][td]

7​

[/td][td]

1/3/2019​

[/td][td]

1/5/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

1/5/2019​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr][td]

9​

[/td][td]

8​

[/td][td]

1/3/2019​

[/td][td]

1/5/2019​

[/td][td]

​

[/td][td]

1/3/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

10​

[/td][td]

9​

[/td][td]

1/3/2019​

[/td][td]

1/5/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

1/3/2019​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr][td]

11​

[/td][td]

10​

[/td][td]

1/3/2019​

[/td][td]

1/5/2019​

[/td][td]

​

[/td][td]

​

[/td][td]

1/5/2019​

[/td][td]

4​

[/td][td]

4​

[/td][/tr]


[tr][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]