+ Reply to Thread
Results 1 to 5 of 5

Lookup for Shift End when Crossing Midnight

  1. #1
    Registered User
    Join Date
    06-10-2020
    Location
    Manila, Philippines
    MS-Off Ver
    2016
    Posts
    2

    Lookup for Shift End when Crossing Midnight

    Hi All!

    I am new to this forum but I hope you could help me on this one.

    I have a raw data that shows the Punches of an employee for the day. The ones that cross midnight on to the next day gives me headache because included in the raw data is the start date and start time for the next day's shift.

    In the example. Yuta Smith's schedule is June 8,2020 11:00 PM- June 9,2020 8:0AM.

    I can capture the Start Date and Start Time. But I am having trouble capturing the End Date(F4) and End Time(G4).
    Attached Files Attached Files
    Last edited by jeclemente1; 06-10-2020 at 10:22 AM. Reason: Attaching Workbook

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Lookup for Shift End when Crossing Midnight

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    06-10-2020
    Location
    Manila, Philippines
    MS-Off Ver
    2016
    Posts
    2

    Re: Lookup for Shift End when Crossing Midnight

    Thanks for the heads up, Ali. Edited the post and attached a file. Cheers!

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,588

    Re: Lookup for Shift End when Crossing Midnight

    Try in cell H2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    5,730

    Re: Lookup for Shift End when Crossing Midnight

    In practice, some one come sooner and leave later schedule, the extra hour/minute should not be taken into account, for instant: In: 22:30 vs 23:00 (30 minutes earlier), Out: 8:05 vs 8:00 (5 minutes later)
    IF you might interest, use below solution:

    For the first row, H2:
    Please Login or Register  to view this content.
    Fron second row, H3:
    Please Login or Register  to view this content.
    Drag H3 down

+ 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. [SOLVED] See if timestamp is within two time values crossing midnight
    By klf in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2019, 04:55 PM
  2. [SOLVED] Userform Time crossing Midnight not working
    By demarc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-17-2019, 10:25 AM
  3. [SOLVED] Formula Needed - Hours between times crossing midnight
    By meowmix7903 in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 08-30-2017, 05:52 AM
  4. [SOLVED] Tiimelog - Time Difference Crossing Over or Past Midnight
    By mycon73 in forum Excel General
    Replies: 6
    Last Post: 10-20-2016, 06:41 AM
  5. formula to calculate time difference crossing midnight
    By ditorejax in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-06-2014, 08:27 PM
  6. Time crossing midnight
    By belle08 in forum Excel General
    Replies: 4
    Last Post: 01-27-2014, 03:47 PM
  7. Replies: 13
    Last Post: 11-04-2013, 02:30 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1