+ Reply to Thread
Results 1 to 3 of 3

Find earliest and latest time based on specific date

  1. 08-05-2019, 09:35 PM #1
    klturi421
    klturi421 is offline
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Find earliest and latest time based on specific date

    I have a list of users with various start and stop times. What I would like to do is find the very first start time for a specific date and the very last time for the same date based on the user selected. For example, there are multiple start times for a user along with multiple stop times within the same date range. What I am looking to do is find the very first start time and very last start time, not the start and stop time for the first and last entries combined.

    I have included an example of the data that I am using along with a tabbed labeled "Ideal" which shows ideally what I am looking for.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-05-2019, 11:01 PM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Find earliest and latest time based on specific date

    Please try at C4:D8

    =IFERROR(AGGREGATE(15-MOD(COLUMNS($B2:C2),2),6,'Raw Data'!C$2:C$58/('Raw Data'!$A$2:$A$58=$C$2)/(INT('Raw Data'!C$2:C$58)=$B4),1),"")
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 08-05-2019, 11:20 PM #3
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: Find earliest and latest time based on specific date

    named ranges:
    start_time ='Raw Data'!$C$2:INDEX('Raw Data'!$C:$C,COUNTA('Raw Data'!$C:$C))
    stop_time ='Raw Data'!$D$2:INDEX('Raw Data'!$D:$D,COUNTA('Raw Data'!$D:$D))
    time_stamp ='Raw Data'!$B$2:INDEX('Raw Data'!$B:$B,COUNTA('Raw Data'!$B:$B))
    Unique_users ='Ideal Output'!$L$2:INDEX('Ideal Output'!$L:$L,COUNTA('Ideal Output'!$L:$L))
    User ='Raw Data'!$A$2:INDEX('Raw Data'!$A:$A,COUNTA('Raw Data'!$A:$A))


    Start:
    Please Login or Register  to view this content.
    Stop
    Please Login or Register  to view this content.
    Last edited by protonLeah; 08-05-2019 at 11:22 PM.
    Ben Van Johnson
    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] Macro to find the Earliest Start Time and Latest Finish Time during a period
    By rehana402003 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2018, 06:40 AM
  2. Finding earliest login and latest logout time for a date
    By amohamedfarook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2016, 03:24 AM
  3. Return earliest/latest date/time to utilize in pivot table If/Index/Match/MIN Functions
    By Neyme in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-09-2015, 12:43 PM
  4. How to find the earliest or latest date with criteria selection?
    By justinbaba in forum Excel General
    Replies: 2
    Last Post: 03-20-2015, 01:55 AM
  5. get earliest Date-time and Latest Date-time based on 2 criterias (first & last name)
    By ag273n in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2014, 06:39 AM
  6. Looking Up Earliest and Latest Time Values
    By warhead92100 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-25-2013, 06:07 AM
  7. Find earliest date based on specific criteria in other columns
    By cat2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2011, 11:50 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