Formula not being Friendly

[Mujer]

Hi Guru's.

My lovely spread sheet has been working nice for the past couple o
weeks and is now playing funny buggers.

I have a formula calculating a countdown timer
(Cell I6) =IF(VALUE(G6)<H6,”Breached”,VALUE(G6-H6)) (displayed a
Breached or 1:24:26)
(Cell G6) is the Due Date (displayed as 28/02/2012 15:13)
(CellH6) is Todays date (displayed as 28/02/2012 15:13)
Now what has started to happen randomly is that when the countdown get
to 0:00:00 Rather than displaying Breached it is adding 24:00:00
Or we have a due date of 10/08/2012 15:52
Todays date as 09/08/2012 15:19
And the calculation is showing 0:33:29

What is going wrong… It is really doing my head in.

So I have somewhat found why it is playing up in that it is onl
calculating the hours until the time counts down (not taking the dat
into account as it was formatted as time)

I tried Formatting the Cell to be Custom [H]:mm:ss - Which then gives m
the cumulative... But I cant figure out where to work "Workdays" int
the formula. And also making this change has also broken my conditiona
formatting.

I have attached the spread sheet for anyone who can help me figure thi
out

+-------------------------------------------------------------------
|Filename: Watch List Data v1 1.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=528
+-------------------------------------------------------------------

 

[Living the Dream]

Hi

Firstly

You do not need to use the Value() in the time formula.
It works just as well without.

=IF(G2<H2,"Breached",G2-H2)

Secondly

Unless you have a 3rd logic eg Low, Medium & High ( of which you only
use Low/Medium), you will only need to do this:

=IF(C2="Medium",WORKDAY(E2,3)+E2-INT(E2),WORKDAY(E2,5)+E2-INT(E2))

And Lastly

In the format screen, select Custom and copy this in

d "Days", hh:mm:ss

It will display 5 Days, 10:20:20

HTH
Mick.

 

[Mujer]

Thanks Mick. That has fixed the count down.

Excel 2007 keeps putting Value into the formula automatically... weir
but I will leave it be.

I'm still stuck with the conditional formatting as I have that based o
the time only and I’m not sure how to modify the formula to take th
day’s component into account.

I am trying to get the following to work:
•If the due date has passed: =I1=”Breached”
•9hrs or less until Breach: =AND(I1<>”Breached”,MOD(I1,1)<=0.3375)
•Between 9:00:01 and 15:00:00
=AND(I1<>”Breached”,MOD(I1,1)>0.3375,MOD(I1,1)<=0.625)
•Between 15:00:01 and 30:00:00:
=AND(I1<>”Breached”,MOD(I1,1)>0.625,MOD(I1,1)<=1.25)
•Greater than 30:00:00: =AND(I1<>”Breached”,MOD(I1,1)>1.25

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Living the Dream]

ok

Rule 1. =I1="Breached"
Rule 2. =I1<=0.375
Rule 3. =IF(I1<=0.625,AND(I1>0.375))
Rule 4. =IF(I1<=1.25,AND(I1>0.625))
Rule 5. =I1>1.25

HTH
Mick.