Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    service schedule (2007)

    I am trying to get excel to tell me when my machinery is due for service:
    I have 6 items of plant, which I get hour meter readings daily, hours range between just over 2000hrs on one to just over 10000 on another.
    Service schedules are 250 hrs 500hrs 750hrs and 1000hrs: I want to be told in a separate column which service is "DUE" 50 hrs before hand and "OVERDUE" after the value -so, "250 DUE" from 6200 to 6250 and "250 OVERDUE" from 6250 to 6300.
    Ideally, the "DUE" cell would go green, and the "OVERDUE" would go red. (wether this all operates in the same cell or side by side cels is ok)
    I would be ok with 4 columns each denoting their service being 150, 500, 750, or 1000, if it cant all be done in one cell
    Another column ideally, I would mark with some value, being "DONE" or a simple tick, and the lookup would see this, and cancel out the "DUE" or "OVERDUE"
    The issue I have is, I want it to read just the hundreds out of the hour meter column, not the thousands, so i can duplicate the formula for all machines, and not have to change everything every thousand hours etc. SO, it would read 2205 hours only as 205 hours and light up "DUE" 250, in the 250hr column, or "250 DUE" (in green)
    Problem is, I really dont have much of an idea.
    I started with this formula =IF(G3>4000,G3-4000,IF(G3>3000,G3-3000,IF(G3>2000,G3-2000,IF(G3>1000,G3-1000,G3))))
    and altered it a fair bit goit some things happening, but really dont know how to match multiple commands in the formula line
    I know it is long winded... and all that.. but, everything helps...and I do learn with each project..
    Thanks .. in advence [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: service schedule (2007)

    OK, I cant reply to myself, but the colour thing is ok now.. found that one..*grins*
    The version is the issue now, I have 2007 on my home comp, BUt 2003 on the computer which holds the program.. so we are looking at a version 2003 problem
    cheers

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: service schedule (2007)

    Welcome to Woody's Lounge!

    See the attached sample workbook. The formulas are rather complicated because I eliminated all intermediate results.
    Select cell G3 (for example) and look at Format | Conditional Formatting to see how the conditional font coloring is specified.
    Column H contains the formulas for Due etc., and you can enter anything in column I (for example an "x") to indicate that the machine has been serviced.

  4. #4
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: service schedule (2007)

    Hans, thank you very much:
    that will work well, i threw it in and "YEP" it will work.. now i can get rid of all the stuff i was blowing to hell and back [img]/forums/images/smilies/smile.gif[/img]
    Thank you
    I see my mate from down south asked about it too.. verada (dean) spoken with him, so thank on both accounts
    kio

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: service schedule (2007)

    You're welcome!

    I was wondering whether there was any relation between your question and verada's. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •