+ Reply to Thread
Results 1 to 10 of 10

Difference in times between adjacent cells in two columns

  1. #1
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    161

    Difference in times between adjacent cells in two columns

    Is there a way to pick up differences in times between adjacent cells in two columns?

    In column F I have start times and a comment, this is usually something like 'Late finish, not legal until 03:45' in the same number cell in column G will be a different time, say '02:30' (Column G only contains times, no comments)

    I would like to pick up if there is a difference in the times and highlight the cells in both columns if possible?

    The information is F7 to F109 and G7 to G109

  2. #2
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    161

    Re: Difference in times between adjacent cells in two columns

    When I read it back I should say I want to pick up if there is ANY difference in the times, NOT the actual time difference, 30 minutes, 45 minutes etc

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Difference in times between adjacent cells in two columns

    1st, if you have any text in a cell, then that whole cell becomes text, and the data in that cell is no longer numeric -calcs cannot be run on text, you need to extract the numbers 1st and convert them to numeric.

    2nd, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Difference in times between adjacent cells in two columns

    Maybe this as a CF rule?

    =LEFT(F7,5)<>LEFT(G7,5)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Difference in times between adjacent cells in two columns

    Ali, looks like 1st col contains text and a time, and 2nd col only time (not sure if that would be text time or not though)

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,374

    Re: Difference in times between adjacent cells in two columns

    Drat! That won't work, then ...

    More coffee!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Difference in times between adjacent cells in two columns

    LOL that gave me a good laugh, thanks

    Thats why I asked for a sample file, so we can see some sample text

  8. #8
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    161

    Re: Difference in times between adjacent cells in two columns

    Thanks for your responses so far and apologies for me not responding...... the joys of working nights!

    The info in the Example of TRG is how we get it as an attachment. (There is information in columns B and C but we never use this) We copy and paste into the TRG Sheets and work from there with the buttons on the right to sort, produce paper copies and print etc.

    I can use conditional formatting to highlight cells in column F in the TRG Sheets to highlight any cell that has a comment, any cell that has a time, class 2 drivers etc but what I really want is to pick up if there is any difference in times from columns F and G. (Again I don't want to pick up its 15 minutes difference etc only that there is a difference and ideally highlight in red the cell in column F and if possible the cell in column G too)

    Example of TRG.xlsxTRG Sheets - Excelforum.xlsm
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Difference in times between adjacent cells in two columns

    This will search for, and extract the time value...
    =IFERROR(--MID(D2,SEARCH(":",D2)-2,5),"")
    It will return "" if there is no tiime.

    To do the comparison, there is a small issue with accuracy and precision. Time is actually a decimal of 1 (day), so 4:00 AM is really just 4/24 or 0.166666666666667. I would suggest that you round both times (inside the calc) to, say 5 or 6 decimal places to compare them

  10. #10
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Edinburgh
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    161

    Re: Difference in times between adjacent cells in two columns

    Thank you for your help FDibbins and everyone else My sheet is getting a bit 'trippy' now with all the colours, I think I will call it a day

+ 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. count how many times we have the same value in two (or more) adjacent cells
    By AlienPump in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-22-2016, 10:48 AM
  2. [SOLVED] Difference Between Two Dates/Times In Two Cells
    By Logit in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-29-2016, 07:26 PM
  3. [SOLVED] Difference between two times - avoiding ###### for times that span midnight
    By Abarency in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2015, 01:15 PM
  4. [SOLVED] Show percentage difference between cells in two columns on the same row.
    By StreekyD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2014, 06:36 PM
  5. Copy the value in a cell, X times to adjacent cells
    By mselbie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 02:00 PM
  6. Replies: 2
    Last Post: 07-03-2013, 06:17 PM
  7. Find percentage of times two adjacent cells have the same data
    By jamie_bisson in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-26-2013, 01:53 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