Tracking Schedule
Hi Experts,
I want to track schedule of list of activities based on date. Following is the scenario.
Please refer to the following data,,,. Based on this scenario... I want to;
Activity End date Status
Create User manual 6/1/2013 Complete
Provide Training 6/3/2013 Complete
Maintain master data 6/10/2013 In Progress
Post transaction 7/1/2013 In Progress
Reporting 7/31/2013 Not Started
place a formula that will give how many tasks are behind the schedule.
For e.g. Today is 06/27/2013, then how many tasks must have been completed and actually how many are completed.
Result for the attached scenario as on 06/27/2013 should be 1.
Please help me design the fomula...
Thank you.
Macro to solve your problem!
I do not know exactly how are those data displayed on your worksheet, but if you put task on column A, date on column B as status on column C you can use this macro to solve your problem.
Sub CountTasksBehindschedule()
Dim Count As Integer
Range("e1").Formula = "=TODAY()"
Count = 0
Range("b1").Select
Do Until ActiveCell = ""
If Range("e1").Value > ActiveCell And ActiveCell.Offset(0, 1) <> "complete" Then
Count = Count + 1
ActiveCell.Offset(1, 0).Select
Else: ActiveCell.Offset(1, 0).Select
End If
Loop
MsgBox Count
End Sub
I hope it helps.
Cátia Santos