1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel formula

Discussion in 'Business Applications' started by Meza, Feb 28, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Meza

    Meza Thread Starter

    Joined:
    Feb 28, 2012
    Messages:
    3
    i have a list of agents with several log ins and log outs for one day, i was trying to do a vlookup to only get the name of the agent and the first log in and the last log out i am not too familiar with this and i got stock can some body please help me with a formula for this, atached you will see the excel sheet with the info that i got from the agents log in and out.

    Thanks in advance.


    <Removed all the names and replaced with EMP - Number - ETAF moderator

    Note: we only need dummy data - and as this is a public forum not a good idea to have real names , i assume they where , in the public domain

    also solution in my last post #4 >
     

    Attached Files:

  2. valis

    valis Moderator It's My Birthday!

    Joined:
    Sep 24, 2004
    Messages:
    75,804
    not entirely sure that that can be accomplished with a forumula; may need a full-on macro for that one. But we'll see what others have to say.
     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    if you entered the times in a 24hr format then a pivot table will work quite easily to get MAX and MIN

    i have been playing with the format to see if we can change the time as you entered into a true time value - but struggling a lot as its a little late and been full on recently

    i created and extraction using mid and removing the last 2 digits ie PM and used timevalue() to change the text to a time and it worked and produced a min and max in a pivot table - but then remembered you had PM and AM !!!!!!

    i'll keep looking at it
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    You need to only use dummy data - so i have added EMP-1 to 9 and removed all the names


    OK so had a play and converted the time to a real time using this
    =TIMEVALUE(MID(C4,1, LEN(C4)-2) & " "& MID(C4,LEN(C4)-1,2))
    =TIMEVALUE(MID(C4,1, LEN(C4)-2) & " "& MID(C4,LEN(C4)-1,2))
    in Column E and F
    and then formatted the column as Custom format HH:MM

    so that turns in a 24 hour clock and real time

    labelled the columns
    Real Login for E
    and Real Logout for F

    now we can use a pivot table and setup Min log in and Max log out - for each user

    i have also formatted the Real Login and Real logout fields as a number custom format HH:MM

    if you dont know pivot tables then maybe a bit difficult to add to
     

    Attached Files:

  5. Meza

    Meza Thread Starter

    Joined:
    Feb 28, 2012
    Messages:
    3
    Thanks etaf this is exactly what i need it and thanks to every one else for the help i just need to study this to make it happen on my head thanks again
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1043023

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice