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

 

[Charry118]

Would this use with Joe's formula?

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:
Code:
=DATEDIF(D1,IF(Z1>0,Z1,TODAY()),"d")
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 had a date picker in the closed out column instead of a drop-down box, this would be dead easy.

Sorry, I meant would this work with Joe's formula?

 

[AliGW]

You've got two choices, really: either you have a separate cell that shows the closing date when the case is closed (you could have a date picker for this) and a formula in column F that freezes the date when a date is entered into the closing date column, or you convert the F cell to a value when that other column is populated, either manually or using a macro.

 

[AliGW]

Sorry, I meant would this work with Joe's formula?[/COLOR]

Yes.

 

[AlanY]

deleted...

 

[Charry118]

Thanks Ali.

I'm unfamiliar with both options (limited knowledge of excel!)... however having a date picker "feels" like it might be the best solution for what I need (macros still scare me!).
Any chance you could explain how to do this? Or point me in the right direction of a tutorial?

 

[AliGW]

There's a tutorial here: Excel Tips: Put a Calendar Date Picker on an Excel Worksheet


I suggested it because you mentioned ease of use.

This may also help: http://www.rondebruin.nl/win/addins/datepicker.htm

 

[Charry118]

That's great thanks

I'll give it a go!

Thanks all for your help!

 

[AliGW]

You're welcome!

Let us know how you get on.