Date !

[muddan madhu]

Hi,

I have 3 columns which states like this
Col A Col B Col C
Start End status
01-jan-2008 - pending
21-jan-2008 - pending
1-Feb-2008 - pending

End date are not specified as it is not completed.
What i need is - if the status is pending then
count the number of days (pending).
Eg., in the above example all are pending, in my
original file I have completed status(which will have end date).

I thought of using this but unable to get the desired result
=today()-Average(pending dates)

Thanks a lot

 

[JLatham]

Try starting with this formula in column C (row 2 formula shown)
=IF(B2="","Pending for " & TODAY()-A2 & " days.","Completed")
This assumes that the entries in column B are totally empty until you enter
a completed date into them. That means no dash as you show in your example.

For any given row, you can see that the TODAY()-A2 part of the formula is
calculating the # of days that it has been pending. You could use this in
another column (say column D) just to show the days each one has been pending:
=IF(B2="",TODAY()-A2,"")
and then take an average of the values in column D to get the average number
of days the tasks have been pending - then "" entries won't be used for the
average. Be sure to format the column as General.

Conversely you can use
=IF(B2<>"",B2-A2,"")
to get the number of days to complete a job and average the results in that
column to get the average number of days to complete it. Be sure to format
the column as General.

Again, system acting up - this may be double-post. Sorry 'bout that if it
happens.