+ 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,639

    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,639

    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
    Office 365, rarely 2007
    Posts
    12,717

    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
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    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 * Add Reputation 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,639

    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,639

    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)

Similar Threads

  1. Excel VBA with Internet Explorer (frames within frames?)
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-17-2014, 10:21 AM
  2. [SOLVED] Divide Time Period across different Time Frames (Calculating Interest)
    By kopapa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 03:09 PM
  3. Formula for Time frames
    By Ricardo9211 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2011, 02:37 PM
  4. converting time frames
    By kylefoley76 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-07-2010, 05:45 AM
  5. [SOLVED] How to Calulculate Hrs and Mins between time frames
    By Corey in forum Excel General
    Replies: 2
    Last Post: 05-30-2006, 03:40 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