Conditional counting of days.
Closed
Josh
-
Jun 6, 2011 at 10:56 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 6, 2011 at 02:37 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 6, 2011 at 02:37 PM
Related:
- Conditional counting of days.
- Skype last seen days ago - Guide
- Time of day clock stopped - Guide
- How to calculate 90 days from date in excel - Guide
- Move files older than x days windows - Guide
- You have 179 days left to request a review ✓ - Facebook Forum
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 6, 2011 at 01:39 PM
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 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?
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 6, 2011 at 02:37 PM
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
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
