Date !
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 |
Date !
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. "muddan madhu" wrote: 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 |
All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com