Close

Results 1 to 4 of 4

Thread: Excel Help

  1. #1
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,777
    Thanks
    274
    Thanked:        365
    Karma Level
    431

    Help Excel Help

    I'm trying to write an excel sheet that when opened, shows how many parts our company should have made at that particular moment (target). We do a 3 shift pattern with 3 sessions each day, 3 lines within our shift all running at different speeds and efficiency. I've got the sheet working as I want it except for one of the shifts, as our night shift starts before midnight and finishes at 7am I cant seem to get the formula working to work out how many minutes have been worked in the first session as this is the session that goes past midnight. I have attached the file so it becomes easier to understand. All my formulas and working out is done on the 'Formulas' tab, the cells coloured green may have to be altered on occasion so if the shift pattern times change, the sheet will automatically adjust to give the correct target. The targets tab is where the info is simplified and shown.

  2. #2
    DF PlaYa pattikins's Avatar
    Join Date
    Jul 2001
    Location
    manchester
    Posts
    795
    Thanks
    251
    Thanked:        82
    Karma Level
    314

    Default Re: Excel Help

    Try this @ E24 on the 'formulas' sheet:
    =IF(AND($B$20>B24,$B$20<"23:59:59"),((HOUR($B$20-B24))*60)+(MINUTE($B$20-B24)),IF(AND($B$20<B24,$B$20<C24),((HOUR(($B$20+1)-B24))*60)+(MINUTE(($B$20+1)-B24)),D24))

    Thanks to pattikins

    Soulassassin (21st June 2015) 


  3. #3
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,777
    Thanks
    274
    Thanked:        365
    Karma Level
    431

    Default Re: Excel Help

    Thanks, that seems to work for the first session but now the 2nd and 3rd sessions are wrong, I didnt notice before. Im looking at them now to see if i can apply the same 'formula'

    Yep got them all sorted and added a sunday nightshift which had 4 sessions and was a longer shift. Took a while but got there. Cheers again pattikins.
    Last edited by Soulassassin; 21st June 2015 at 05:14 PM.

  4. #4
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: Excel Help

    OEE oh how much fun I have had with that subject ;-)

    Been doing some consultation work for a small company and implanting an OEE dashboard in Excel (as a prototype with the idea of transferring to ASP/SQL) but the company has just been bought out, the larger company is pretty impressed with what they are running so might get quite a bit more work ;-)

Similar Threads

  1. [REQUEST] Excel help
    By Fear345 in forum PC Software
    Replies: 7
    Last Post: 19th August 2017, 10:25 PM
  2. Excel help
    By BigBrand in forum PC Software
    Replies: 8
    Last Post: 28th April 2015, 11:03 AM
  3. Excel Help
    By Conkers in forum PC Software
    Replies: 2
    Last Post: 9th February 2010, 09:57 AM
  4. MS Excel Help
    By ss30 in forum PC Software
    Replies: 2
    Last Post: 2nd January 2007, 12:10 PM

Social Networking 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
  •