Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 5
Default Formula not being Friendly

Hi Guru's.

My lovely spread sheet has been working nice for the past couple of 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 as 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 gets 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 only calculating the hours until the time counts down (not taking the date into account as it was formatted as time)

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

I have attached the spread sheet for anyone who can help me figure this out.
Attached Files
File Type: zip Watch List Data v1 1.zip (27.8 KB, 65 views)

Last edited by Mujer : August 9th 12 at 07:38 AM Reason: additional information
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 151
Default Formula not being Friendly

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.
  #3   Report Post  
Junior Member
 
Posts: 5
Default

Thanks Mick. That has fixed the count down.

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

I'm still stuck with the conditional formatting as I have that based on the time only and I’m not sure how to modify the formula to take the 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.2 5)
•Greater than 30:00:00: =AND(I1<”Breached”,MOD(I1,1)1.25)
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 151
Default Formula not being Friendly

ok

Rule 1. =I1="Breached"
Rule 2. =I1<=0.375
Rule 3. =IF(I1<=0.625,AND(I10.375))
Rule 4. =IF(I1<=1.25,AND(I10.625))
Rule 5. =I11.25

HTH
Mick.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array-friendly functions vsoler Excel Worksheet Functions 1 September 12th 09 04:33 PM
Bar chart No printer friendly. ramkumar_cpt Charts and Charting in Excel 0 February 10th 06 06:43 AM
how to get hyperlink from the friendly name in xl. jae Excel Discussion (Misc queries) 1 January 20th 06 07:16 PM
user friendly filter Vincent[_4_] Excel Programming 3 August 5th 04 09:59 PM
Not very printer friendly libby Excel Programming 0 November 13th 03 01:19 AM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"