I'm trying to work on a text table in which I am tracking NCEP phase on-time completions broken down by submission date. An NCEP has 4 phases that are tracked for timeliness - Initial Containment, Risk Assessment, CAPA Escalation, and Closure. I have been able to create a table for each phase individually which tracks the number of late phases submitted by plant broken down by the respective phase submission date. Now I need to create an overall table which tracks number of all late phases by plant. My issue is that I don't know how to break it down by month. Each column needs to include Initial Containment submissions, Risk Assessment submissions, CAPA Escalation submissions, and Closure submissions for each month. So for example, if there are 5 submissions for each of the 4 phases in September 2018, the overall denominator should be 20. I've attached some sample data and below are my current calculations:
*will use [Phase] in place of Initial Containment/Risk Assessment/CAPA Escalation/Closure for ease of formula description. See attachment for actual naming conventions.
Phase Tasks Completed: COUNT([Phase Completed/Approved Date])
Late Tasks - Phase: IF [Phase Completed/Approved Date] > [Phase Due Date] THEN 1
Late Tasks - NCEP Phase: SUM([Late Tasks - Phase])
% On-Time Tasks: 1 - ([Late Tasks - NCEP Phase] / [Phase Tasks Completed])
Any help would be greatly appreciated, let me know if you have any additional questions.
Would you have a packaged workbook with the attached data to show the formulas tried and give a bit better idea at what you are trying to do here? This was posted over a month ago so you may have already found a solution but we want to check and see the status and help the community to assist if it is still an open question.