# New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

## Stop counting days if another cell is filled out

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

### Stop counting days if another cell is filled out

Hello Everyone!

I'm new to the forum and I'm requesting assistance with a formula. I run a report for my company that measures progress (typed manually) per work station. I'm currently trying to calculate production times per area using a start date, the today() function and the inputed progress percentages.

Is there a formula that will help me calculate the time spent per station and stop calculating days when the process is completed?

I have used a conditional formula but doesn't seem to work:

=IF(F8="","",IF(F8<100%,\$O\$5-\$S\$3,IF(F8=100%,\$S\$3-\$O\$5)))

I have attached a sample spreadsheet to illustrate. I'm grateful for any help that you can provide.

Best to all!
• 0

You do not have the required permissions to view the files attached to this post.
Rookie

Posts: 2
Joined: Feb 16, 2017
Reputation: 0
Excel Version: Excel Mac 2011

### Re: Stop counting days if another cell is filled out

I didn't find anything wrong with your formula, but I re-wrote it to look like this:-
Code: Select all
`=IF(SUM(F8),IF(F8<1,\$O\$5-\$S\$3,\$S\$3-\$O\$5),0)`

The reason why I used SUM(F8) is explained in the Excel tip I posted a little while ago. The system told me it is awaiting approval from the moderator.
I tweaked the logic a little. F8 is either 0 (blank) or <1 or >=1. In your formula the case of F8>1 isn't covered.

On second thought, since IF(F8<1,\$O\$5-\$S\$3,\$S\$3-\$O\$5) just ensures a positive number as a result, you may be able to do the job with this formula.
Code: Select all
`=IF(SUM(F8),ABS(\$O\$5-\$S\$3),0)`
• 3

Have a great day!

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator

Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

### Re: Stop counting days if another cell is filled out

Sisyphus, thank you very much for taking the time to reply.

The problem I see is that the calculations are tied to the volatile TODAY() formula. Your formula, as well as mine, will display the same result. But, every day I open the file the calculated time (in days) will keep increasing. I would like for the calculated days to stop once the task reaches 100% (or 1 if you prefer).

Do you have any other thoughts? By the way, I'm using Mac excel 2011.

Thanks Again!
• 0

Rookie

Posts: 2
Joined: Feb 16, 2017
Reputation: 0
Excel Version: Excel Mac 2011

### Re: Stop counting days if another cell is filled out

I see.
I didn't figure out your logic, but presuming that you wish to enter the percentage of completion rather than calculate it, you should have a start date and a variable end date. Therefore the use of TODAY() or S3 must be conditional. With this logic you should arrive at a formula like this one.
Code: Select all
`=IF(SUM(F8),IF(F8<1,TODAY(),P5)-O5, 0)`

Translated, If there is no entry in F8 return 0. (You could return "" if you so prefer.)
If the entry is <1 (meaning less than 100%) take TODAY(), else take P5 as end date.
You might use S3 if you prefer, but that looks like more work.
To calculate the number of days passed, deduct the start date from the end date.

If you wish to show the result as a negative number I suggest to multiply the entire thing conditionally with -1.
Code: Select all
`=IF(SUM(F8),(IF(F8<1,TODAY(),P5)-O5)*IF(F8<1,-1,1), 0)`

Instead of negative numbers, you might like to consider using conditional formatting to colour the cells using the same condition.

I know this isn't the whole deal, but I hope my suggestions help.
• 0

Have a great day!

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator

Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010