+ Reply to Thread
Results 1 to 7 of 7

Calculate lead time based on available working hours

  1. 07-08-2014, 09:32 AM #1
    Nu2Java
    Nu2Java is offline
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Calculate lead time based on available working hours

    Hello, I am trying to find a formula that will calculate lead time in a shop. I have work that comes into the shop during all hours of a 24 hour day, but this shop has a working time from 7:00am to 5:00pm. So I need to be able to determine the lead time based on the available hours in the shop. Every example I have found gets messed up when jobs get logged in at say 1:00am. In many cases jobs will come in at 4:00pm and do not leave until say 10:00am the next day. The shop start and stop time never changes.

    I would be grateful for any help on this. Thanks
    Reply With QuoteRegister To Reply
  2. 07-08-2014, 10:09 AM #2
    Nu2Java
    Nu2Java is offline
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Calculate lead time based on available working hours 

    Please Login or Register  to view this content.
    This is the formula I am using now. It gets me close, but when the incoming time is after shop stop time and ends the next day, the formula fails.
    Reply With QuoteRegister To Reply
  3. 07-08-2014, 10:10 AM #3
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,632

    Re: Calculate lead time based on available working hours

    Well, The problem seems to be well described, but shall we prepare test file?
    I think you'd better prepare one with some special cases as you mentioned above - "jobs will come in at 4:00pm and do not leave until say 10:00am" and similar. And do not forget to include in a sample file "manually assesed" output. As well as - how you deal with date (if you do)?
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  4. 07-08-2014, 11:30 AM #4
    Nu2Java
    Nu2Java is offline
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Calculate lead time based on available working hours

    Thanks for the reply... Attached is the best example I can give. Hopefully this makes sense.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-08-2014, 03:17 PM #5
    Nu2Java
    Nu2Java is offline
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Calculate lead time based on available working hours

    Anyone? Please help!
    Reply With QuoteRegister To Reply
  6. 07-09-2014, 11:19 AM #6
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,632

    Re: Calculate lead time based on available working hours

    A bit long one, but shall work:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    here:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    we skip start day if after 5pm
    and here
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    take 7am if start time is outside working hours - and only within working hours MOD(B2,1)
    Reply With QuoteRegister To Reply
  7. 07-11-2014, 03:04 PM #7
    Nu2Java
    Nu2Java is offline
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Calculate lead time based on available working hours

    Kaper.... Thanks for your help with this. It seems to be working just the way I need it to.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate response time in working hours between two dates
    By ajagibson in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-28-2021, 08:45 AM
  2. [SOLVED] Calculate time for working hours when start time falls outside of working hours
    By SKDY_Beau in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-20-2014, 12:50 PM
  3. Calculate finish time given start time, working hours and job duration
    By swanseaexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2012, 03:00 PM
  4. Calculate response time in working hours between two dates
    By ajagibson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-28-2012, 01:21 PM
  5. calculate the amount of time between 2 dates (working hours)
    By rickyRRE in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-23-2012, 10:03 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1