Excel Pivot - Date Created, Date Closed

Question

Hello

I have two columns. Date created, Date closed.

I can generate a count of date created by dragged date created in to rows, then date created in to values. This gives me a count, for each month, of how many were created.

On each row, the date created and date closed can be different months or even years but the data doesn't represent in to the correct month.

E.g. I want

2022

• Jan
• Feb

-Mar

-Apr etc.

Then I want to see how many were created in Jan 22, vs how many were closed with a date in Jan 22.

A row may be

01/01/2022 for created, but 01/01/2023 for closed.

This would give a 1 for Jan 22 and a 1 for Jan 23.

At the moment, it is showing both in Jan 22.

I expect this is because I am using the date created months for the rows, but how can I get around this? I'm having a brain fog!

Answer 1

Hi,

Enter this formula in cell D2 to generate a table of the first day of each month from both columns

=SORT(UNIQUE(EOMONTH(TOCOL(A2:B11),-1)+1))

Thereafter you may use the COUNTIFS() function suggested by HansV.

Hope this helps.

Answer 2

Let's say the dates are in A2:B32.

In D2:

=SORT(UNIQUE(A2:A32-DAY(A2:A32)+1))

.

In E2:

=COUNTIFS($A$2:$A$32,">="&$D2,$A$2:$A$32,"<"&EDATE($D2,1))

In F2:

=COUNTIFS($B$2:$B$32,">="&$D2,$B$2:$B$32,"<"&EDATE($D2,1))

Fill down from E2:F2.

Answer 3

Or a pivot table again, with Date Created in the Rows area, grouped by Months and Years, and both Date Created and Date Closed in the Values area.

Answer 4

Thank you for this. The issue here is I'd like to output to a column chart so that I can show side by side for each month, a column chart showing created and closed.

Is that still possible please?

Answer 5

Add Date Created to the Rows area, Date Closed to the Columns area. Group both on Months and Years.

Add either of these (or another column) to the Values area.