+ Reply to Thread
Results 1 to 11 of 11

Min / Max Time with multiple rows

  1. #1
    Registered User
    Join Date
    12-07-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2007
    Posts
    6

    Question Min / Max Time with multiple rows

    Good morning all,

    I am new here and pretty good with excel but having a roadblock on one of the Dashboards I am creating.

    I have a list of people with multiple rows that have times in them. I need to be able to capture JUST the earliest time and the latest time for each person.


    See below. Bill Smith has several rows for one day. He logged into the system at 9:00 (I need to capture that) And then logged out and back in a few times this day. His last log out was at 17:04. I need to capture that as well.



    Capture.PNG

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,217

    Re: Min / Max Time with multiple rows

    Attach a sample file with some data.
    I think you should be able to use Min and Max formulas.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Registered User
    Join Date
    12-07-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2007
    Posts
    6

    Re: Min / Max Time with multiple rows

    Hmm, when I click on attachments it does't open up any type of options to choose a file. Just a small thin box.

    Capture.PNG

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,248

    Re: Min / Max Time with multiple rows

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    12-07-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2007
    Posts
    6

    Re: Min / Max Time with multiple rows

    Thank you so much Pete!

    Attached is the sample file.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,217

    Re: Min / Max Time with multiple rows

    See attached file.

    Formula used to capture log-in:
    =MIN(C2:C13)

    For Log out:
    =MAX(D2:D13)

    For total time from log-in to log-out is diff.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-07-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2007
    Posts
    6

    Re: Min / Max Time with multiple rows

    Thanks modytrane.
    I should have specified that there are multiple employees and dates on the spreadsheet So I imagine using some sort of sumifs, index, or something along those lines along with the min arguemnt for log in and then a separate formula with the max argument for log out
    I would be looking up
    If employee name (Bill Smith)
    If this date (12/6/2018)
    Give me the minimum of the log in times in column C

  8. #8
    Registered User
    Join Date
    12-07-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2007
    Posts
    6

    Re: Min / Max Time with multiple rows

    Here is a better example of the data file I would be working with. My apologies for the earlier incomplete one.
    Attached Files Attached Files
    Last edited by PeterMcAteer; 12-07-2018 at 02:57 PM.

  9. #9
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    2,090

    Re: Min / Max Time with multiple rows

    maybe using these 2 array formulae
    =MIN(IF((B2:B49=K1)*(INT(C2:C49)=K2),C2:C49))
    =MAX(IF((B2:B49=K1)*(INT(D2:D49)=K2),D2:D49))
    Like
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-07-2018
    Location
    Philadelphia, PA
    MS-Off Ver
    2007
    Posts
    6

    Re: Min / Max Time with multiple rows

    Thanks Fluff13!
    I think that may do the trick. I will play with that and let you know if there are any issues.

  11. #11
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    2,090

    Re: Min / Max Time with multiple rows

    You're welcome & thanks 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)

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