Stop counting days if another cell is populated

[Charry118]

Hi All,

First off, I am a self taught excel user, so some of my formulas may go around the world a bit!

I need some help with a spreadsheet I use on a regular basis.

The sheet is used to track customer complaints and how long the investigation takes to complete, therefore the following applies;
D column = Date Raised
E column = KPI Completion date (D1+7)
F column counts the actual number of days to complete the investigation using this formula... =DATEDIF(D1,TODAY(),"d")
Currently I have to manually write the final count in F when the investigation is closed out, however I would like this cell to stop counting when AB1 is "Yes"

I'm not sure if I will need to completely change the formula in the F column.

Any help would be greatly appreciated.
Thanks

 

[AliGW]

Just to clarify, what do you want to appear in column F once it has stopped counting? Is it the number of days the case took?

 

[Charry118]

Just to clarify, what do you want to appear in column F once it has stopped counting? Is it the number of days the case took?

Yes, that's right.

 

[Joe4]

Welcome to the Board!

Currently I have to manually write the final count in F when the investigation is closed out, however I would like this cell to stop counting when AB1 is "Yes"

Do you have some cell that tracks/captures the Date that the investigation was closed out?
If so, we could easily incorporate that into our formula.
If not, it will probably require some VBA code to capture the date when someone enters "Yes" in AB1, and then either stores that date somewhere to use in the calculation on the sheet itself, or overwrites your formula in cell F1 with the hard-coded value of the calculation.

 

[AliGW]

If you click inside the cell in column F and press F9, the formula will be converted to a value. Can you work with that?

 

[Charry118]

Welcome to the Board!

Do you have some cell that tracks/captures the Date that the investigation was closed out?

Thanks Joe!

No, I don't - but this could easily be added to the sheet if that would make the formula easier?
Currently I have a column labelled "Closed out" which is a drop down menu for "yes" or "no".

The sheet is used by various different people and I wanted it to be as simple and user friendly as possible.

 

[AliGW]

If you had a date picker in the closed out column instead of a drop-down box, this would be dead easy.

 

[Joe4]

Let's say that you had column Z as your close out date, and that is only populated when it is closed out.
Then you could change your formula to:

[COLOR=#333333]=DATEDIF(D1,IF(Z1>0,Z1,TODAY()),"d")[/COLOR]

What this would do is if there is a value in Z1, it will use that in your date calculation. Otherwise, it will use the Current date.

 

[Charry118]

If you click inside the cell in column F and press F9, the formula will be converted to a value. Can you work with that?

Hi Ali,

I'm sorry - I'm not sure I follow.

With the formula I'm using in F I currently have a value of "42661" until column D is populated (date appears). Is this what you mean?

 

[AliGW]

Not sure I'm entirely clear. Can you paste your layout here? You can use the ForumTools add-in in my sig line to get tables into your post easily.