Difficult but do-able?

J

Jaydubs

In a sheet in which I keep track of questions coming in, I not the date in
and date closed.

in another sheet I want to check how many questions were raised in a month
and how many closed in the same month.

Each question has its own line.

How can I best tackle this?
 
S

Stefi

If on sheet1 you have these columns:
A: question
B: date in (format as date)
C: date closed (format as date)

and on sheet2
A: months (format as number)
B: questions raised
C: question closed in month of raising

Then in sheet2
B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2))
C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2),--(MONTH(Sheet1!C$2:C$9)=A2))

Regards,
Stefi
 
J

Jaydubs

Great thanks very much for this super answer.

This is what I was looking for, but now I see that some of the questions are
carried over to the next month........For instance a question is raised on
the last day of the month and answered on the first day of the next month,
how do I take this into consideration?? As these will not appear in the
formula given by you !
 
R

Roger Govier

Hi

Formula 2 given to you by Stefi, gives the number raised and closed in
the same month, which, as you rightly point out will omit those raised
lat month, but closed this month.

Either, change formula 2 to take out the test for month raised e.g.

C2: =SUMPRODUCT(--(MONTH(Sheet1!C$2:C$9)=A2))

or leave C2 as it is, and have another category in D2 for those closed but not raised in same period which would be

D2: =SUMPRODUCT(--(MONTH(Sheet1!C$2:C$9)=A2)) - C2


Regards

Roger Govier
 
S

Stefi

My solution is an answer exactly to what you asked. If I were you I would
measure the elapsed time from date in to date closed:

Regards,
Stefi
 
J

Jaydubs

Hello Roger,

Great, This solves my question. Thank you very much !!

Kind regards,

Jay
 
J

Jaydubs

Hello Stefi,

Thank you very much for your support.

Indeed you served me with the answer for my original question, ffor which I
am thankfull.

When I checked the outcome, I realised I did not take this into account that
unanswered questions may be caried over into the next month. That is why I
posed my second question, which Roger answered.

So again, thanks for your answer !!

Kind regards,

Jay
 
Top