Conditional counting of days.

Closed
Josh - Jun 6, 2011 at 10:56 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 6, 2011 at 02:37 PM
Hello,
If you could help with some coding in MS Excel, it would be really appreciated.

I want to basically have a cell that starts counting the difference between two days. I know I can use the DAYS360 function for Excel 03. But I also want a way to stop the counting, once say a user enters a value in another cell.

Thanks in advance for any help,
Josh

2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jun 6, 2011 at 01:39 PM
You want to know the difference between two cell and stop counting if some other cell is filled ? Difference between two days are always there, so not sure what is counting doing here.

I think what you want to do is, show the difference if a certain cell is empty and dont show the difference if that certain cell is not empty. If that you can use an IF statement
some thing like this
=IF(A1="","", B1-c1)

If that is not what you want, you need to explain your issue with less ambiguity
I just reread my post and realized how vague it is.

I'll be specific.
I'm basically tracking a process in Excel. THe process is 20 days long and divided into 5 stages, each consisting of 4 days. I want to track all my projects through this process so that I can see which stage of the process is slowing things down.
Each stage has 4 days.
My initial idea was that based on the start date of the project, each stage column would have this in their cells "=DAYS360(start date, current date)". This would give me the number of days in that the project is in that tage of the process.
However I need Stage 1 to stop counting and hold the number of days it has, when the process moves into Stage 2. Stage 2 will then start counting and then stop when stage 3 starts.
I realize there is a gap, and I figured that stage 2 cells would have something like this pseudo code"=days360(startdate, startdate+num of days in stage1". That part I can get. The hard part is figuring out how to stop the counting in the currentstage and start counting in the next stage.
Thats when I came up with placing something similiar to an If(null) statement. where the current stage will stop counting, if a zero is inputed into the next stage.

Any clearer?
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jun 6, 2011 at 02:37 PM
Yes a lot. There can be many approaches. The two that comes to my mind at the moment are

1. This is based on assumption that for each of 5 stages you would a cell to mark the end of that stage. If that is possible, you can enter the date in the "stage completion marker" cell when that stage complete. So essentially, if the cell is empty it gives you difference between start date and today and if the cell has some value if can give you difference between start date and that date
=DAYS360(Start date, IF(target cell = "", NOW(), target cell))

2. Other approach can be macro based. Where when you put the marker to indicate completion of cycle it converts the formula into value

Hope it helps