Bonus Points!!!
Bonus Points!!!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Bonus Points!!!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    No one could answer this yesterday, so it must be really hard. Only a true Excel genuis can help me...

    I have a spreadsheet with one row per day.
    Every day I record the time we start and stop painting, and when we change air filters. I need an elegant way to calculate how many production hours passed since the last filter change. We don't paint non-stop, the filter change time varies, and some days one or the other doesn't occur.

    Date Start Stop Filter changed
    3/08/02 6:00 AM 2:30 PM 12:50 PM
    3/09/02 6:00 AM 12:30 PM None
    3/10/02 No Production
    3/11/02 6:00 AM 2:00 AM 6:00 AM
    3/12/02 6:00 AM 2:00 AM 1:00 AM
    3/13/02 6:00 AM 2:00 AM 4:00 PM
    3/14/02 6:00 AM 2:00 AM None
    3/15/02 6:00 AM 2:30 PM 11:00 AM
    3/16/02 No Production
    3/16/02 No Production
    3/17/02 6:00 AM 2:00 AM 1:00 AM

    Example results would be
    3/13 filter changed after 9 hours
    3/15 filter changed after 15 hours

    I am using a nested IF/THEN, but there is not enough room to jump over more than 6 non-production days. I need to keep all the days, because I have other sheets linked by OFFSET formulas.

    Thanks
    John H.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Is your example correct.
    Are the times correct.
    Start at 6: end at 2: is 20 hours?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I couldn't make sense of your data. It appears to be space delimited, but "No Production" contains a space. Couldn't figure out which column contained this value. If your values include spaces you should choose another delimiter such as a comma.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It looked great when I set it up, must be a variable sized font. I'll try again, delimited by ";"

    We have two shifts. First shift is 5 days, 8 hrs/day, 6:00 AM to 2:30 PM. Second shift is 4 days, 10 hrs/day, 3:30 PM to 2:00 AM. There are two 1/2 hour lunch breaks (11:30-12:AM and 7:00-7:30 PM) and one hour between shifts (2:30-3:30 PM), but I thought that would really be a killer to add. Sometimes we work one shift on Saturdays, sometimes we shut down for a week. Holiday schedules get weird.

    Date Start Stop Filter changed
    3/08/02; 6:00 AM; 2:30 PM; 12:50 PM
    3/09/02; 6:00 AM; 12:30 PM; None
    3/10/02;;; No Production
    3/11/02; 6:00 AM; 2:00 AM; 6:00 AM
    3/12/02; 6:00 AM; 2:00 AM; 1:00 AM
    3/13/02; 6:00 AM; 2:00 AM; 4:00 PM
    3/14/02; 6:00 AM; 2:00 AM; None
    3/15/02; 6:00 AM; 2:30 PM; 11:00 AM
    3/16/02;;; No Production
    3/16/02;;; No Production
    3/17/02; 6:00 AM; 2:00 AM; 1:00 AM

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Suppose your data (as shown above) is in A1:D12. The formula to calculate cumulative "Post Maintenance Production Hrs" could be entered into E2 as...

    =IF(ISNUMBER(D2),C2-D2+(C2A2+(D2<0.25)+D2)/24,N(E1)+C2-B2+(C2
    ...and copied down to E12. Format column E as...

    [h]:mm

    This is such an elegantly designed sample data set I'm guessing it's literally a "text book" example; therefore, I'll let you do some of the [home] work and figure out how to factor in lunch hours. At least this should get you started!

    Some documentation...

    1. ISNUMBER(D2) is TRUE if filter was changed
    2. C2-D2+(C2 3. -(C2="2:00"+0)*(A2+"2:30 P">A2+(D2<0.25)+D3)/24 deducts 1 hour if filter was changed before 1 hour inter-shift "break"
    4. N(E1)+C2-B2+(C2 5. -(D2="None")*(C2="2:00"+0)*(C2<0.5)/24 deducts 1 hour if inter-shift "break" occurred on a day when a filter wasn't changed

    [ This Message was edited by: Mark W. on 2002-03-22 06:33 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    consider in maint col

    =IF(D7>0,(D7-B7+(B7>D7))+G6,"")

    Carry fwd column G

    =IF(D7>0,(C7-D7+(D7>C7)),(C7-B7+(B7>C7))+G6)

    ensure you use custom format [h:]mm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com