Counting down H:mm from 50:00 to show minus hou

[nje]

Thanks to the help provided by a few folks on these forums I have made a lot of progress but when trying my masterpiece out, I noticed a couple of niggles. I can live with them but hope that someone could point me in the right direction .
I have experimented but was in danger of ruining everything. I used this very successful formula provided by Oldchippy
=IF(J3="","",IF(J2="",L1,L2)-J3) (no criticism of the formula)
but then realised that once it reached zero hours it just displayed ########
No problem with the formula but my fault entirely for not realising or explaining exactly what I wanted.
The purpose is to count down from 50:00 hours to monitor engine hours. Then I remembered that sometimes the engineer can give an extension to the hours (if they are too busy to inspect it) The extension could be up to 10 hrs. So, ideally the countdown would go into minus hours.
Is this possible? I could just change the 50:00 at the top of the column to a higher number if an extension is given but It would look nicer if it could go into minus. I have included an attachment for you to see how it's progressing. While I'm on here, whilst experimenting yesterday I've managed to loose the headers and access to the right hand side of my work book. and can't remember how I did it. any ideas? Before anyone says it What a plonker!!

[martindwilson]

there are no minus times unless you use the 1904 date system
see here
http://j-walk.com/ss/excel/usertips/tip051.htm

[nje]

Thanks Martin
I have changed to the 1904 date which then ruined out my date coloumn, but it's the times that are important, so I formatted the date column to 'text' and that seems to work. Then, I did a conditional format to change the hours with a minus in front of them to show up in 'red'. I think I'm statrting to get the hang of this, well, the simple stuff anyway.
I've attached the updated version, can you see any problems with what I've done and do you know how I can undo the hiding of the headings etc?

I've just checked the attachment and for some reason the 'red' formatting of the column has been lost. I have done it 3 times and it works but not when I attach it

[martindwilson]

im not sure abour your conditional formatting in column m!
you use the same formula for all 3 conditions
=NOT(ISERROR(SEARCH("Service",M3)))
this means only the first condition will ever be used
also you could just use =ISNUMBER(SEARCH("Service",M3))
or even just =SEARCH("Service",M3)
the second and third conditions must use different criteria

[nje]

Thanks, I'll look at that.
I actually use excel 2010 and in that you can use more formulas so it was only when I saved it to excel 2003 that I lost some of the formatting if that makes sense.

[nje]

Just had a look at column 'M' I''m completely baffled. as far as I understand the 3 conditions are
1)The text remains white (out of sight) until 6 hours before 50 hours expires,
2) Then the words'Service due' pops up.
3) Then the words 'Service due' changes colour

It does work, and I wouldn't have a clue what to do with your formula or how to go about changing the criteria for the other two conditions. But I will have a play with it to see what happens

[martindwilson]

hmm thats not what i saw save it as xlsx and repost ill have a look in 2007

[nje]

I've attached the other version.
The reason for saving it as 2003 was because some can't view it otherwise.

Thanks for looking

[martindwilson]

ok for some reason you have 6 formats in the cells only the first will take effect see cf 1.jpg
you dont really need it to look for "service due" you can just work from the formula the generates "service due anyway" =IF(L4>TIMEVALUE("6:00"),"","Service due")
so the conditional format is using the "use a formula to determine which cells to format"
=L4<=TIMEVALUE("6:00"). however what you have works, just unecessary

[nje]

OK, thanks for that, I understand now I've seen .jpg . I've printed your formula out and will have a go at it later this evening. It's always good to try different ways. Thanks for your time and experience

[nje]

You are working well, I have now had a go with your formula and it does simplify matters and works well. Thanks