Tracking Corrective Actions using an Excel sheet

[raghu_1968]

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.

 

[Ninja]

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?

 

[Randy Lefferts]

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]

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.

 

[Jen Kirley]

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.

 

[Mahesh5277]

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]

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.

 

[the47guy]

Here is one I use. I am still tweaking it each and every day.