+ Reply to Thread
Results 1 to 31 of 31

Equipment hour meter monitoring

  1. #1
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Equipment hour meter monitoring

    Hi people.

    I have 2 series one with dates number ex. (1,2,3,4,5,6,7,8) and one with equipment hourmeter increasing numbers example(2,10,34,56,70,80,100,130), and i want to find the formula which will show me which date the equipment complete 25 working hours, and mark that box with a color.
    Thanks

    Moderator's note: Thread moved from Outlook subforum. --6StringJazzer
    Last edited by 6StringJazzer; 02-15-2014 at 09:51 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    not sure i understand the dates as 1 or 2 or 3 etc

    If you want the cell where column B is 25 or greater and highlight Row A

    for 2007 or 2010 excel version
    Conditional Formatting

    Highlight applicable range >>

    A:A


    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:

    =B1>=25

    Format… [Number, Font, Border, Fill]
    choose the format you would like to apply when the condition is true
    OK >> OK
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    I want to be highlighted every 25 hours (We make service every 25 hours).
    Thank you very much

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    I'm sure theres a better way to do this
    =IF(OR(ISTEXT(B1),B2=""),0,IF(FLOOR(B2,25)<>FLOOR(B1,25),1,0))

    then you need to change the apply range from A:A
    to a range A2 : A????
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    Thank you very much for the response

    But I am very confused.

    Is problem if I make my question one more time!!


    Hourmeter (reading) 2, 10, 34, 56, 70, 80, 100, 130,


    I need to be highlighted every 25 hours. So the numbers which has to be coulerd are (34,56,100,and130)

    Thank you again

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    why doesn't 80 highlight ?
    every 25 hours

    34, 56, 80, 100, 130

    which is what my example does

  7. #7
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    yes you are right 34, 56, 80, 100, 130,

    but I have series not column

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    can you post a sample spreadsheet - not sure what you mean by a series?

  9. #9
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    November 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 monthly daily
    Hourmeter reading 100 150 150 250 270 300 310 210 7
    Daily 7 14 21 28 35 42 49 56 63 70 77 84 91 98 105 112 119 126 133 140 147 154 161 168 175 182 189 196 203 210





    I need the numbers from D8 to AG8 to be highlighted every 25

  10. #10
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    How I can attach file here!!!

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    =IF(OR(ISTEXT(D7),D8=""),0,IF(FLOOR(D8,25)<>FLOOR(D7,25),1,0))
    Corrected
    =IF(OR(ISTEXT(C8),D8=""),0,IF(FLOOR(D8,25)<>FLOOR(C8,25),1,0))
    and select the row D8 to AG8 to apply to the conditional formatting

    a spreadsheet would help a lot as requested

    go advanced> manage attachments>
    Last edited by etaf; 02-15-2014 at 08:30 AM.

  12. #12
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    thank you again for helping me ..
    Attached Files Attached Files

  13. #13
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    sorry - last formula still on rows dah!!!!
    =IF(OR(ISTEXT(C8),D8=""),0,IF(FLOOR(D8,25)<>FLOOR(C8,25),1,0))

    BUT why not 77 highlighted ?
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    sorry I forgot 77 this is what i needed

    THANK YOUUUUUU

    you help me very very much ....

    I want to make this a bit more complicated but when you have free time

  15. #15
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    ok - just post the question(s) if related to this conditional format - otherwise a new post if a different subject

    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

    maybe worth changing the title to something more in keeping with the question

    1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

  16. #16
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    I posted it so when you have free time

    Thank youuu

  17. #17
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    you will have to setup 4 conditional formats
    i have the rules set out here

    BUT are these values likely to change and colour change???
    if so you may need to use a lookup to get the value and then use a condtion for a colour

    so before coding it all - can you explain

    otherwise look at
    I14 to I17
    and thats the rules for each colour which need to be setup as a formula for conditional formatting

  18. #18
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    No the values will not change.

    we make service type A (Yellow) after the first 25 hours, after the second 25 hours we make service type B (Green), after the third 25 hours we make again service type A (Yellow), after the 4th 25 hours we make service type C (Blue), after 5th 25 hours we make sercice type A (Yellow), after the 6th 25 hours we make service type B (Green), after the 7th 25 hours we make service type A (Yellow), after the 8th 25 hours we make service type D (Red). And after we repeat this again and again.

    Thank you

  19. #19
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    And after we repeat this again and again.

    so for 225 would be yellow
    250 green
    etc
    is that correct, whats the maximum number it would go to ?
    not sure of a pattern to it ?

  20. #20
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    yes right 225 yellow and 250 green and 275 yellow 300 blue.

    I dont know the maximum, mabe 3 or 4 cycle per year, or mabe the machine get brake down and the services stop.

  21. #21
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    and 400 would be red ?

  22. #22
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    yes you are right

  23. #23
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    so i have developed the yellow to use a table - see rows

    24:27
    and then if that works for you - we could put the table on a different sheet

    I'm sure theres a much betterway of doing this
    are you ever going to go above 600 ?

    and will this always be in the same row 8

  24. #24
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Equipment hour meter monitoring

    try this......
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    thank you very much for the all helping etaf...

  26. #26
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    your welcome - did it all work out ok?

  27. #27
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    Yes I think is ok, but i will know for sure after 15 days when I insert the monthly data

  28. #28
    Registered User
    Join Date
    02-15-2014
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Equipment hour meter monitoring

    Hi

    How can I compare the data of two columns and if the same to show me the number from a third column?
    I upload a quick sample

    Thank you for helping Excelforum
    Attached Files Attached Files

  29. #29
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    what two columns are you comparing and what third column

    can you show some examples of the results you expect please

  30. #30
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Equipment hour meter monitoring

    Withdrawn, obviously not what you want.
    Last edited by newdoverman; 04-19-2014 at 06:05 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  31. #31
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Equipment hour meter monitoring

    sorry, i'm not understanding what you need

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Equipment Check In/Out Help!!
    By butkus35 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 09:16 PM
  2. Calculating APR
    By hman27 in forum Excel General
    Replies: 1
    Last Post: 03-12-2012, 07:34 PM
  3. Replies: 1
    Last Post: 08-12-2010, 05:00 PM
  4. Help: calculating hours for various equipment
    By RiverTam in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-10-2007, 04:15 PM
  5. [SOLVED] convert decimal numbers to a fraction of an hour for payroll hour
    By Flower in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2006, 03:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1