+ Reply to Thread
Results 1 to 11 of 11

Identifying duplicate time frames

  1. #1
    Registered User
    Join Date
    07-06-2015
    Location
    North East England
    MS-Off Ver
    Office 2013
    Posts
    4

    Identifying duplicate time frames

    Hi all,

    I have a spreadsheet with multiple time stamped entries for people. These people can clock in or out in several places at the same time. trying to write a formula or macro (not sure which achieves the aim best in this case) that will search the data and highlight in red lines with times that over lap for a specific person. Is this possible? and is it something somebody could help with please? i've copied a few lines below as an example. many thanks

    excel queston.jpg

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Identifying duplicate time frames

    There appears to be something wrong with your example picture. In row 4, the IN date and time is LATER than the OUT date and time.

    Attach a sample workbook instead of a picture. Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

    Remember to desensitize the data.

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

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    07-06-2015
    Location
    North East England
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Identifying duplicate time frames

    thanks newdoverman, example worksheet attached.

    the previous picture is correct however. the sheet is the combination of data from several collection points that all work offline, hence the ability, if somebody is abusing the system, to clock in in several places at once - the hoped for formula is to help identify this in sheets of thousands of entries without having to manually sort through each person.

    many thanks again
    Attached Files Attached Files
    Last edited by martyncitrus; 07-06-2015 at 03:34 PM. Reason: error in attached sheet

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,066

    Re: Identifying duplicate time frames

    You may try this to see if you get the desired output....
    Please Login or Register  to view this content.
    Last edited by sktneer; 07-06-2015 at 11:32 PM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    07-06-2015
    Location
    North East England
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Identifying duplicate time frames

    Hi sktneer, thanks for replying.

    i'm not sure i understand your response or what to do with it?

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,066

    Re: Identifying duplicate time frames

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,882

    Re: Identifying duplicate time frames

    Another way ... by formula. With a helper column in column I. If I understand the problem correctly this formula in the helper column should do it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I've also applied it to the Before sheet to verify that it works there, too.

    With the Applies to: in Format Manager set to A2:H5 and using formula to set criteria with this formula: =$I2>1

    set your background color and apply.

    Workbook is attached.
    Attached Files Attached Files
    Last edited by FlameRetired; 07-06-2015 at 10:58 PM.
    Dave

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,142

    Re: Identifying duplicate time frames

    Dim t ?

    What was that supposed to be? I didn't see you used the variable "t" in your code.

    Did I miss something?
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,066

    Re: Identifying duplicate time frames

    Thanks Marvin, You have pointed this out correctly.
    That variable is not used at all. I declared it in the beginning and thought I would use it later but didn't use it.

  10. #10
    Registered User
    Join Date
    07-06-2015
    Location
    North East England
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Identifying duplicate time frames

    Hi all,

    macro worked great, many thanks, and thanks for the "novice" instructions

    first time using this forum but will definitely be back! great support

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,066

    Re: Identifying duplicate time frames

    You're welcome.

    If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

+ 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