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