+ Reply to Thread
Results 1 to 7 of 7

Looking for MAX formula when times span or cross midnight

  1. #1
    Registered User
    Join Date
    08-04-2012
    Location
    Rochester, New York
    MS-Off Ver
    Excel 2010
    Posts
    13

    Looking for MAX formula when times span or cross midnight

    This issue is when the person clocks their last punch after midnight which is on the next day. I use the Route Start to make sure the MIN formula is not pulling those after midnight punches from the previous day's route(with a little buffer in case the route starts early). After some searching I cant find a way to have excel continue looking into the next day for the last punch. At most the next days punches could be up to 5:00 AM. A better method might be to continue looking for the next punch until the gap is more than 2 hours.

    The table is A to D and the sum fields are E to I. Shown is what the formulas are currently producing. What cell H2 should show is 0:12:17 and cell I2 should be 2:27:37

    G- {=MIN(IF(($A$2:$A$4=E2)*($B$2:$B$4=F2)*($C$2:C$4>(D2-TIME(2,0,0))),$C$2:$C$4))}
    H- {=MAX(IF($A$2:$A$4=E2,IF($B$2:$B$4=F2,$C$2:$C$4)))}
    I- =H2-G2

    A B C D E F G H I
    Route Date Punch Route Start Route Date Start Time End Time Cycle Time
    Loc1 2/1/2016 21:44:40 21:00:00 Loc1 2/1/2016 21:44:40 23:32:13 1:47:33
    Loc1 2/1/2016 23:32:13 21:00:00
    Loc1 2/2/2016 0:09:45 21:00:00
    Loc1 2/2/2016 0:12:01 21:00:00
    Loc1 2/2/2016 0:12:17 21:00:00

    Thanks in advance!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Looking for MAX formula when times span or cross midnight

    Please include a sample spreadsheet. It will help see the problem better.

    I think you have to address both the date and time as a whole value to get the time rather than try to figure out if punch out time is less than punch in time. As you noted, that might lead to detecting the previous morning's punch out time.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-04-2012
    Location
    Rochester, New York
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Looking for MAX formula when times span or cross midnight

    Attached is a few days example of two different routes. Going over midnight does not happen every day. I was able to account for the post-midnight values from the MIN formula that gets the route Start Time. Table 1 is the individual values from the time punches and Route Breakdown by Day is the summation of the punches. In all yellow highlighted cases the punches go past midnight but are not counted toward the cycle time.

    I am also having an issue counting the number of punches in a 'day' because of the midnight span as well but I assume once the problem is solved I'll be able to use the solution twice.

    Here is the count formula I'm using which would normally go in column G:
    {=SUM(IF(FREQUENCY(IF('Table 1'!$E$2:$E$353=$F2,MATCH('Table 1'!$A$2:$A$353,'Table 1'!$A$2:$A$353,0)),ROW('Table 1'!$A$2:$A$353)-ROW('Table 1'!A2)+1),1))}
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Looking for MAX formula when times span or cross midnight

    The that span midnight is calculated in your example on the Route Breadown by Day with this formula in E2 filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Format as h:mm

    If you mean Punch in and Punch out, why not just use the Day Count and multiply by 2 because for every Punch In there is a Punch Out and you seem to have 1 entry for each Punch In Punch Out combination.
    Would this work for your count?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

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

    Re: Looking for MAX formula when times span or cross midnight

    To define the date+time range with given Route, there are 3 criterias:

    A) Route:
    'Table 1'!$A$2:$A$353=$A2

    B) Date+time range ('Table 1'!$B$2:$B$353+'Table 1'!$C$2:$C$353) is WITHIN given date (B2) at 9:00PM (G2) AND next day at 7:PM (G2-2)
    'Table 1'!$B$2:$B$353+'Table 1'!$C$2:$C$353>=$B2+$G2
    and
    'Table 1'!$B$2:$B$353+'Table 1'!$C$2:$C$353<=$B2+$G2+22/24

    Criterias combination to create a time array:
    IF(('Table 1'!$A$2:$A$353=$A2)*('Table 1'!$B$2:$B$353+'Table 1'!$C$2:$C$353>=$B2+$G2)*('Table 1'!$B$2:$B$353+'Table 1'!$C$2:$C$353<=$B2+$G2+22/24)=0,"",'Table 1'!$B$2:$B$353+'Table 1'!$C$2:$C$353)

    Now using MOD(MIN(time array),1) to get MIN

    MIN in C2:
    Please Login or Register  to view this content.
    MAX in D2:
    Please Login or Register  to view this content.
    Cycle in E2:
    =D2-C2+(D2<C2)

    C2 and D2 are array formula, should be confirmed by Ctrl-Shift-Enter
    Attached Files Attached Files
    Quang PT

  6. #6
    Registered User
    Join Date
    08-04-2012
    Location
    Rochester, New York
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Looking for MAX formula when times span or cross midnight

    Holy Man Bebo that is some bit of logic and a great explanation. Some super solid code which worked like a champ. Really appreciate it. Newdoverman, thanks for the upgraded cycle time code.

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

    Re: Looking for MAX formula when times span or cross midnight

    You are welcome. Tkx for the feedback.

+ 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] Difference between two times - avoiding ###### for times that span midnight
    By Abarency in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2015, 01:15 PM
  2. Count if the condition applies 2 or more times in a time span:
    By EMyk08 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2014, 09:25 AM
  3. getting the difference bewtween two times accounting for midnight cross over
    By superchew in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-12-2013, 11:30 PM
  4. Calculating hh:mm between two times that span midnight
    By Ymir20000000 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-27-2011, 09:55 AM
  5. Replies: 1
    Last Post: 06-30-2010, 07:48 PM
  6. Times and midnight issues
    By bslob in forum Excel General
    Replies: 1
    Last Post: 12-08-2007, 01:36 AM
  7. Replies: 1
    Last Post: 03-08-2005, 11:39 AM

Tags for this Thread

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