+ Reply to Thread
Results 1 to 6 of 6

Scheduled Start and finish times

  1. 08-20-2014, 12:08 PM #1
    Kramxel
    Kramxel is offline
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Scheduled Start and finish times

    Hi All,

    I have attached an example workbook to help explain the issue i'm attempting to resolve at the moment. What I am trying to achieve is:

    * for each line where an agent exists (for example LC), find the last occurrence of "0" in the first chain of 0's, look up the start time from the header in row 2 and place this in the column marked start.
    * for each line find the first occurrence of 0 in the second of chain of 0's and enter the end time in the column marked finish.
    * for each line enter the start and finish time for each period of L which may take up one or two adjacent cells.

    Any help would be gratefully received I've been staring at the same sheet for 3 hours and haven't made any progress yet.

    Thank you.
    Reply With QuoteRegister To Reply
  2. 08-20-2014, 12:48 PM #2
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,649

    Re: Scheduled Start and finish times

    Try this.....

    In Ag3 (Array Formula requires confirmation with Ctrl+Shift+Enter instead of just Enter.)
    Please Login or Register  to view this content.
    and copy down.

    In AH
    Please Login or Register  to view this content.
    and copy down.

    In AI3 (again an Array Formula requires CSE)
    Please Login or Register  to view this content.
    and copy down.

    Is this what you are trying to achieve?
    Attached Files Attached Files
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  3. 08-21-2014, 03:41 AM #3
    Kramxel
    Kramxel is offline
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Scheduled Start and finish times

    Hi Sktneer,

    Its very close to what I need (mainly because I didn't explain it very well) however this does 99% of the work I couldn't do. (I just need trim the results to get a singular start and finish times)

    It doesn't look like the lunch time is showing as I intended (again probably because of my poor explanation).

    In the original sheet agent CR has a lunch from 11:30 to 12:30 (K15:K16) I would need that displayed in AH15 rather than just the K15 result?

    Thank you for this though it has got me the majority of the way there, really appreciate this.

    EDIT: I've nailed it by combining you're 1st and 3rd formula, changing the search reference and using a countif check to determine if there is a "L" in the shift pattern

    Please Login or Register  to view this content.
    Thank you again for this you're a legend!
    Last edited by Kramxel; 08-21-2014 at 03:53 AM. Reason: progress made
    Reply With QuoteRegister To Reply
  4. 08-21-2014, 04:26 AM #4
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,649

    Re: Scheduled Start and finish times

    Please find the attached sheet to see if you get your desired output now.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 08-21-2014, 08:00 AM #5
    Kramxel
    Kramxel is offline
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Scheduled Start and finish times

    Thank you that's perfect, saved me an absolute headache!
    Reply With QuoteRegister To Reply
  6. 08-21-2014, 08:03 AM #6
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,649

    Re: Scheduled Start and finish times

    You're welcome. Thanks for the feedback.
    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 between start and finish times to pay a shift penalty
    By jonas245 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2013, 05:46 PM
  2. Formula count of hours between start/finish times
    By robenson in forum Excel General
    Replies: 3
    Last Post: 02-14-2011, 07:01 AM
  3. calculate time start and finish times and deducts breaks
    By jasonjkd in forum Excel General
    Replies: 1
    Last Post: 06-25-2010, 06:57 AM
  4. Counting hourly occurences between start and finish times
    By tombally in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-26-2008, 08:33 PM
  5. use Now() in two cells for start and finish times
    By DaveM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2006, 07:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1