+ Reply to Thread
Results 1 to 14 of 14

Looking Up Earliest and Latest Time Values

  1. #1
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Looking Up Earliest and Latest Time Values

    Hi Guys,

    I need advise on how to make this spreadsheet work.

    http://www.mediafire.com/?l5c9wb73cns9rt0

    Sorry for the link, the file is tagged to be to large. Anyway, I need to search for the earliest time in Sheet1!E:E, this would need to be filtered by dates in Sheet1!G:G.

    Does anybody have ideas on how to make this work?

    Thanks in advance for any help.

  2. #2
    Registered User
    Join Date
    07-09-2012
    Location
    India
    MS-Off Ver
    Excel 97/2003/2007/2010
    Posts
    67

    Re: Looking Up Earliest and Latest Time Values

    can you please upload a sample sheet, your link is not opening from my office, it is blocking.

  3. #3
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Looking Up Earliest and Latest Time Values

    How do I attach my file again? There's no option for it now.

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Looking Up Earliest and Latest Time Values

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Looking Up Earliest and Latest Time Values

    Thanks!
    Btw, example is in Sheet3!C2, it needs to show the earliest time from Sheet1!, with Filters of Employee 1 and Oct 1.

    Again thank you for any help or idea you can give.
    Attached Files Attached Files

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Looking Up Earliest and Latest Time Values

    A simple way could be this.

    In M2 of sheet1 and copy down use this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you like hide this column.

    Then in C2 of your other sheet and copy down and across use this ARRAY formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Format these cells as Time.

    -- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
    Not just Enter.

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Looking Up Earliest and Latest Time Values

    Or, perhaps this array formula in C2 and copy across and down:

    =TEXT(MIN(IFERROR(1/(1/(IF(Sheet1!$K$2:$K$5226=CONCATENATE(0+Sheet3!C$1,Sheet3!$A2),MOD(Sheet1!$C$2:$C$5226,1)))),"")),"h:mm AM/PM")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Looking Up Earliest and Latest Time Values

    Thanks! Rep added! however, will there be a way for me to set that if time is PM, use MIN, if time is AM use MAX? or another way around is the check column in Sheet1, as only valid entry wound be from 2F and Time in is in PM (Time out is AM). Sorry for making this too troublesome.

    Thanks again in advance for any help!

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Looking Up Earliest and Latest Time Values

    Sorry - whose solution are you referring to? Mine or Fotis's?

    Regards

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,697

    Re: Looking Up Earliest and Latest Time Values

    Pl see attached file.
    Formulas in E & G columns in Sheet1 is changed.I feel this is what is required.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Looking Up Earliest and Latest Time Values

    Quote Originally Posted by XOR LX View Post
    Sorry - whose solution are you referring to? Mine or Fotis's?

    Regards
    Any of the two, or whichever can be flexible enough to have many criteria.

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl see attached file.
    Formulas in E & G columns in Sheet1 is changed.I feel this is what is required.
    Thanks! However, I tried to update the formula in G column to:

    Please Login or Register  to view this content.
    As I need to filter the entries to only those that are from door 2F-Prod Door. But it doesn't affect the values in Sheet3. Is there any way to tweak the formula to add the door filter?

    Thanks a lot guys,

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,697

    Re: Looking Up Earliest and Latest Time Values

    Filter for Entry from door 2F-Prod Door or Exit from door 2F-Prod Door or for both.

  13. #13
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Looking Up Earliest and Latest Time Values

    Is the formula not sensitive to blank cells? the formula above kinda filters the door, making non 2/f entry blanks. As the source actually being generated on a real time basis I would need to have a check point to filter the 2/f data without deleting the other entries. sorry for this troublesome request.

    TIA!

  14. #14
    Registered User
    Join Date
    12-30-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Looking Up Earliest and Latest Time Values

    Anyone?

    Thanks for any ideas and advice.

+ 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. How to Get Earliest Time In and Latest Time Out
    By domzki225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-20-2013, 04:55 PM
  2. Getting the earliest and latest date in Pivot Table
    By dluhut in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-02-2013, 01:54 PM
  3. Earliest and Latest Time where cell contains certain text
    By asalbus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2013, 01:45 AM
  4. [SOLVED] Calculate time durations for a roster, and indentify the earliest and latest times
    By bajdr47 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-25-2012, 04:47 PM
  5. Excel 2007 counting of earliest and latest time
    By pvesterberg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2010, 03:21 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