Tracking Corrective Actions using an Excel sheet

raghu_1968

Involved In Discussions
#1
I am not familiar with MS Excel and i need a help for tracking the actions.

Is it possible to do the following using the MS Excel 2007 version?

For example : Approval date for the action is 20/06/2013 and the committed date of closure is 25/06/2013.

Today's date is 27/06/2013,
Case 1. As on date the overdue is 2 days. Then the overdue days 2 has to be displayed in the cell.
Case 2. If the action is completed on 23/06/2013, then the respective cell has to be blank.

Please refer the attachment also.

Thanks.
 

Attachments

Elsmar Forum Sponsor

Ninja

Looking for Reality
Trusted Information Resource
#2
In your "Overdue" cell, you would write a logical test.

Assuming your Due Date is Cell A1, and your "Done" date is cell A2:

Your Overdue Days cell might look like:
=If ( A2 > A1, A2 - A1, "" )

The first is a logical test (A2>A1 ?)...If yes, it subtracts the dates (A2-A1)...If no, it leaves the cell blank ( "" ).

Is this what you are looking for?
 
R

Randy Lefferts

#3
I am not familiar with MS Excel and i need a help for tracking the actions.

Is it possible to do the following using the MS Excel 2007 version?

For example : Approval date for the action is 20/06/2013 and the committed date of closure is 25/06/2013.

Today's date is 27/06/2013,
Case 1. As on date the overdue is 2 days. Then the overdue days 2 has to be displayed in the cell.
Case 2. If the action is completed on 23/06/2013, then the respective cell has to be blank.

Please refer the attachment also.

Thanks.
You could also use the following:

=IF(E4="",TODAY()-D4,"")

E4 = Actual Date of Closure on your sheet.
D4 = Expected Date of Closure on your sheet.

So this would check to see if an issue is closed and if there is a date in there, it returns a blank. If it doesn't see a date, it subtracts the expected date of closure from today's date and displays that.

Now, this will return negative days for those that haven't passed the expected date of closure. Sort of a countdown and gives you a visual as their dates approach.

HTH.
 

Ninja

Looking for Reality
Trusted Information Resource
#4
I mocked up the first three rows of your sheet with a nested if statement that checks first if the job is complete, then measures from either today or the completion date as needed.

Nothing fancy...just a sample of what you can do. Expand on it as appropriate.
 

Attachments

Jen Kirley

Quality and Auditing Expert
Leader
Admin
#5
raghu_1968, there are corrective action tracking logs in the Post Attachments List (see the green button in the header) with this functionality already in place.
 
M

Mahesh5277

#6
I mocked up the first three rows of your sheet with a nested if statement that checks first if the job is complete, then measures from either today or the completion date as needed.

Nothing fancy...just a sample of what you can do. Expand on it as appropriate.
hi Ninja,
can you help us to get the status as 'Completed'; if the actual date of closure is same as the committed date of closure.

Thanks,
Mahesh K
 

Jen Kirley

Quality and Auditing Expert
Leader
Admin
#7
hi Ninja,
can you help us to get the status as 'Completed'; if the actual date of closure is same as the committed date of closure.

Thanks,
Mahesh K
I added to the "if" statement to indicate closure if the date is less than or equal to the target date. If the date is met I made the cell show "0" so as to not give the impression the formula was deleted.
 

Attachments

Thread starter Similar threads Forum Replies Date
L Non Conformance Register (aka Tracking Nonconformances and Corrective Actions) ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 8
I Corrective Action Tracking for Product and Process in the same system? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 6
J Software and Methods for Tracking CAPA (Corrective and Preventive Action) items US Food and Drug Administration (FDA) 3
A Corrective Action Tracking - What Measureables and How to Track Status ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 11
P Issue Tracking and CAPA (Corrective and Preventive Action) Management Nonconformance and Corrective Action 6
C Tracking Corrective Action - Preventive Action - Simple Product/Documentation Change Nonconformance and Corrective Action 1
Randy Corrective / Preventive Action Tracking - Creating a spreadsheet Nonconformance and Corrective Action 14
J Web Based Corrective Action Tracking Tools - Does anyone have any knowledge of? Nonconformance and Corrective Action 4
A ISO certs from suppliers - Expiration Tracking Method Various Other Specifications, Standards, and related Requirements 9
M Change control on Tracking Sheet ISO 13485:2016 - Medical Device Quality Management Systems 11
D Tracking software versions used with instruments ISO 13485:2016 - Medical Device Quality Management Systems 0
K Benchmarking and tracking Nonconformities - Template? Benchmarking 1
Q Gage calibration / tracking software General Measurement Device and Calibration Topics 5
Q Looking for a 5S tracking database Problem Solving, Root Cause Fault and Failure Analysis 4
M Tracking Expiration dates on compliance certifications REACH and RoHS Conversations 2
M Proof of distribution - FedEx/UPS tracking numbers Manufacturing and Related Processes 2
J 21 CFR 821 Medical Device Tracking Requirement 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 2
J Is Device Tracking Card an actual requirement under FDA regulation? Other US Medical Device Regulations 0
J Designing new gauge tracking software IATF 16949 - Automotive Quality Systems Standard 4
Q 21 CFR 821 Medical Device Tracking 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 1
Q Tracking Procedure Revisions (Document Control) Document Control Systems, Procedures, Forms and Templates 9
samer Acceptable limits for Spills - Tracking Hydraulic Spills ISO 14001:2015 Specific Discussions 3
M Standards Subscription Service and Standards Tracking Service Pros and Cons? Other US Medical Device Regulations 3
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
R Software change control process and defect tracking ISO 13485:2016 - Medical Device Quality Management Systems 1
I Tracking Protoype Changes; 8D or simple document revision? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
S Tracking laptop movement inside the office IEC 27001 - Information Security Management Systems (ISMS) 7
J Training Record Template and tracking Document Control Systems, Procedures, Forms and Templates 1
S Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
Q Tracking savings - Long term projects and the day to day decisions Preventive Action and Continuous Improvement 5
J Tracking-Calibration Software Calibration and Metrology Software and Hardware 3
A Order Confirmations 7.4 - Tracking Purchase Order Confirmations from Suppliers AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 4
M Tracking External Oversight at a large Naval Shipyard Quality Manager and Management Related Issues 9
J Using a Rhode FSH6 Tracking Generator General Measurement Device and Calibration Topics 2
D Gage Calibration Tracking in MS Excel Calibration and Metrology Software and Hardware 9
A Tracking Calibration and Qualification in an MS Excel Spreadsheet Calibration and Metrology Software and Hardware 5
K Processing Time Tracking Software Program Help Needed Lean in Manufacturing and Service Industries 3
C Reagent tracking in an ISO 17025 laboratory ISO 17025 related Discussions 2
N What is the Cost of Tracking Non-Conforming Product Nonconformance and Corrective Action 5
P Need suggestion on Software or App for Tracking Quality Plan Status Software Quality Assurance 1
A Ensuring Tooling Use Tracking and Traceability AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 1
D Tracking response time with individual label I.D. Statistical Analysis Tools, Techniques and SPC 6
I Tracking changes in ISO Standards Other Medical Device Related Standards 12
G Tracking Employee Training Software Training - Internal, External, Online and Distance Learning 7
B Need help tracking training for my business Quality Assurance and Compliance Software Tools and Solutions 3
K Fast Response Tracking Board (QSB+) Customer and Company Specific Requirements 6
L OTD (On Time Delivery) Tracking and Reporting Misc. Quality Assurance and Business Systems Related Topics 13
J Tracking and Traceability of Finished Goods ISO 13485:2016 - Medical Device Quality Management Systems 6
S ISO9001:2008 - Effectiveness Tracking for Nonconformance ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 1
A Identifying and Tracking Customer Specific Requirements Customer and Company Specific Requirements 1

Similar threads

Top Bottom