+ Reply to Thread
Results 1 to 8 of 8

Help with VBA time lookup Macro

  1. #1
    Registered User
    Join Date
    10-03-2013
    Location
    california
    MS-Off Ver
    Excel 2007
    Posts
    33

    Help with VBA time lookup Macro

    Hi,

    I am fairly new with VBA. I can work with simple macro functions but this one is a doozy.

    I have a sample workbook attached and, The raw data contains timelogs and i need in figuring out a way to find the time in Column F in relation to the Personal Break in column G.
    I have multiple variables for the lookup which made it difficult for me
    Name (Column C)
    Date and Time (Column F)
    Type (Column G)

    On the workbook, I have created a pivot table that gives the desired result somewhat but is not accurate to a tee. If the person went out at lets say 9:49AM and went back at 10:01AM The pivot will show them as separate actions instead of one.

    So a macro can help, Can someone help me pull the data of Personal Break IN to match the Personal Break out? Put blanks instead if the person did not time in/out.
    On the sample workbook, I have the RAW Data on one tab and the desired result on the OUTPUT tab.

    Thank you.
    Attached Files Attached Files
    Last edited by moxman; 02-10-2019 at 08:03 PM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    3,816

    Re: Help with VBA time lookup Macro

    Hi, welcome to the forum.
    I haven't downloaded your file but at first if you read time values they'll always be different. You could add a helper column that if used in the pivot table would group the data.
    E.g. add a column next to the time and just show the hour you will be able to filter on hours, the same with minutes
    Hope I'm making sense
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you ( It doesn't hurt )

  3. #3
    Registered User
    Join Date
    10-03-2013
    Location
    california
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Help with VBA time lookup Macro

    Quote Originally Posted by Keebellah View Post
    Hi, welcome to the forum.
    I haven't downloaded your file but at first if you read time values they'll always be different. You could add a helper column that if used in the pivot table would group the data.
    E.g. add a column next to the time and just show the hour you will be able to filter on hours, the same with minutes
    Hope I'm making sense
    Hi, Thank you for response.
    I had created a helper column to filer the hours but what's bogging my mind is when the hour shifts to another hour, the pivot cant match the Ins and Outs together.

    Totally lost at this one. Any suggestions would be much appreciated

  4. #4
    Registered User
    Join Date
    10-03-2013
    Location
    california
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Help with VBA time lookup Macro

    I created another helper column, this time, i am counting the number of instances per day.
    It was better than the hourly filter but it still wont match the correct time.
    Is there a way for me to lookup and match these to the closest proximity?
    Missing Entry.PNG

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    3,816

    Re: Help with VBA time lookup Macro

    Nah, that won't work, I just looked at your file and well it isn't easy.
    First, where does the data source come from?
    Is that a punch clock or similar?
    You will have to do quite some coding and the idea would be to not do it by hour but maybe person and date.
    If anyone of us goes into this it will be loads of code.
    Maybe use powerquery, I haven't used it but it seems to make life simple.
    I'll see if I can come up with something but don't hold your breath

  6. #6
    Registered User
    Join Date
    10-03-2013
    Location
    california
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Help with VBA time lookup Macro

    Quote Originally Posted by Keebellah View Post
    Nah, that won't work, I just looked at your file and well it isn't easy.
    First, where does the data source come from?
    Is that a punch clock or similar?
    You will have to do quite some coding and the idea would be to not do it by hour but maybe person and date.
    If anyone of us goes into this it will be loads of code.
    Maybe use powerquery, I haven't used it but it seems to make life simple.
    I'll see if I can come up with something but don't hold your breath
    Thank you for helping out. I really ran out of ideas in this one.
    The timestamps came from the Timekeeping tool which uses excel time to record the breaks.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    3,816

    Re: Help with VBA time lookup Macro

    The thing is you would have to add the events in the same row
    Let's say if you go into MEETING and after a certain period you go Out of the meeting you would have to add that in as an extra two columns on the same row of the IN to meeting.
    So the macro would have to analyse a start of an event an look for the next end of that event and add that to that row.

    Load the sheet with raw data and then add a sheet to create the logs for IN and out.
    It's quite some coding you'll have to do.
    There is post with the word 'PunchClock' in the title. search for it. It's not the the same but it does create an excel file for the start adn when you do it for the same user again the end.
    In your case your have more actions per staff member but maybe it gives you an idea

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    3,816

    Re: Help with VBA time lookup Macro

    I wrote a macro for you.
    Run the macrocreateLogReport
    Attached Files Attached Files

+ 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