Monthly Response Report

Hello,

We keep a tracking log that indicates the date an RFI is issued and then the date that it is answered. By contract we have 10 business days to answer the RFI. I need to generate a report in another new tab what our response time is on a monthly basis (business days). I'd also like to know how many RFIs opened in that month, how many we closed and how many total remain unanswered on the whole project.

Anyone able to help?

Thanks

Perhaps with a pivot table. I'm sure someone can help but your question is pretty vague and not much can be done without seeing a sample of your data.

Hello

i assume what you have is a worksheet with dates stores either by row or coloum.

What you need to do is to copy the entire period you are interested in to a sorting page, so if it is a month you might copy 80 rows/coloums, then underneath these 80 rows/coloums count the number of days used in the month you are interested in, using MONTH(). you then use this to count the number of rows you used to add to get a total. This total then can be used for your report.

so it would basically say 12 rows = month 12 and 30 rows= month 11 so offset by 12 sum next 30 rows give you the total of those cells which are in november.

Originally Posted by StephenR

Perhaps with a pivot table. I'm sure someone can help but your question is pretty vague and not much can be done without seeing a sample of your data.

I'd stick to Stephen's suggestion and group dates by months in the PT like here

( BTW what is an RFI?)

Hi
Thanks for your input. I appreciate your directions. I check out what you suggested this weekend.

An RFI stands for 'Request for information'. In construction when the Contractor has a question about the drawings or needs more information about the construction of a specific situation they need to put the question in writing so that it can be formally be answered by the Consultants (architect and Engineers). I'm the architect on this particular project who has to track the flow of information.

I think this may be simpler than I explained. I attached my file for you to see what I mean and I included comments . I was thinking if would be more of an If statement - If is is 01 month then count this column etc.

I obviously don't know what I'm doing but I can build a hospital!

Thank and have a great weekend!
Jennifer

I obviously don't know what I'm doing but I can build a hospital!

OK ! If nobody solved your problem I'll take it up in a couple of hours.
I see there are a lot of merged cells ! Me no like that !

Hi,

Thanks for all your direction. Unfortunately I have been unable to get the things you all suggested to work. After more research online I have been trying an Array formula but I haven't been able to get it to work either - it keeps giving me #Ref. Can someone take a look and tell me what I am doing wrong?

Thanks I appreciate it.
Jennifer

I created some of the formulas to get you started ( see attached)