PDA

View Full Version : Reporting Items in every month they were open, based on creation and closure date.



The_excell_initiate
2017-04-05, 12:54 PM
Hey Guys,

Was hoping someone could help with this. I have a set of data somewhat similar to this:



Open Date
Close date
Item
Owner


01/01/2017
05/02/2017
Item 1
Peter


05/02/2017
07/02/2017
Item 2
John


05/02/2017
15/02/2017
Item 3
Peter


24/01/2017
15/03/2017
Item 4
John



What I am trying to achieve is a pivot table that shows the following




John
Peter


January
Count of items
Count of items


February
Count of items
Count of items


March
Count of items
Count of items



So what I need is a way for excel to identify all the months it was active , for example item 4 was active in January, February and March. So I would need it to report under all three months.
I know I can do this by splitting them onto individual lines and adding another column, but i do expect excel is smarter than this. :D

Hope someone can help.

p.s. running version 2013

p45cal
2017-04-10, 11:44 PM
See attached.
I'd have difficulty getting a pivot table to do this, so formula instead.
Formula in H5 is array-entered (Ctrl+Shift+Enter, not just Enter):
=SUM(((IF(($B$2:$B$5<EDATE($G5,1)),$B$2:$B$5,EDATE($G5,1)))>(IF($A$2:$A$5>$G5,$A$2:$A$5,$G5)))*($D$2:$D$5=H$4))
and copied down and across.
I feel there must be a more elegant formula.