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

Postby SM_FAD » Thu Feb 16, 2017 3:58 pm

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.
SM_FAD
Rookie
 
Posts: 2
Joined: Feb 16, 2017
Reputation: 0
Excel Version: Excel Mac 2011

Re: Stop counting days if another cell is filled out

Postby Sisyphus » Sat Feb 18, 2017 2:00 am

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 translated 100% as 1. Perhaps your function didn't work because of the cell formatting in your sheet. Unfortunately I couldn't download your workbook to check. Basically, Excel stores "100%" as 1, 90% as 0.9 etc. Then you adjust the display of 1 as "100%" or 1.00 or whatever by formatting the cell. The display changes but not the cell's value. If you inserted "100%" as a string (compare my tip mentioned above) it will not be recognised as the number 1, and that might be why your formula didn't work for you. Check your cell formatting. Avoid entering the percentage sign. If the cell is formatted correctly, Excel will add it, and if you add it manually Excel may not understand your intention.
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! :D

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

Postby SM_FAD » Sun Feb 19, 2017 6:51 pm

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

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

Re: Stop counting days if another cell is filled out

Postby Sisyphus » Mon Feb 20, 2017 4:36 am

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! :D

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


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 90 guests