+ Reply to Thread
Results 1 to 16 of 16

Compare two date/time columns

  1. 12-11-2013, 04:43 PM #1
    sulbaran
    sulbaran is offline
    Registered User
    Join Date
    10-25-2012
    Location
    Baton Rouge
    MS-Off Ver
    Excel 2010
    Posts
    20

    Compare two date/time columns

    Hi guys

    Need some help to compare two columns with Date/Time data. The goal is to identify and count the studies done the same day as ordered but before the 19:00 hours. I have two columns that include Dates/Times for each study ( but I also can generate separate columns for date and time if needed ) and will like a formula or a code that allow me to identify all the studies that were ordered one day before the 19:00 but was done the next day for whatever reason .

    Attached is a screenshot with explanation of my problem. I did try to use conditional formatting but didn't work.
    Any help is very welcome...
    Thanks
    Feli
    SAME DAY AS ORDER.jpg
    Reply With QuoteRegister To Reply
  2. 12-11-2013, 05:32 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,943

    Re: Compare two date/time columns

    To attach a Workbook

    (please do not post pictures of worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 12-11-2013, 06:07 PM #3
    sulbaran
    sulbaran is offline
    Registered User
    Join Date
    10-25-2012
    Location
    Baton Rouge
    MS-Off Ver
    Excel 2010
    Posts
    20

    Unhappy Re: Compare two date/time columns

    Sorry
    Forgot to include the file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 12-11-2013, 07:29 PM #4
    FDibbins
    FDibbins is offline
    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
    53,048

    Re: Compare two date/time columns

    I dont find any that dont meet your test, but maybe I'm missing sometning. All your date/times are actually text....

    =(C2*1)<=INT(B2*1)+1+19/24
    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
    Reply With QuoteRegister To Reply
  5. 12-11-2013, 08:26 PM #5
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,943

    Re: Compare two date/time columns

    Yellow (same day post 7pm): 
    Please Login or Register  to view this content.
    Red (next day): 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 12-11-2013, 09:59 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two date/time columns

    I added two helper columns to get values for the day and time. They are shaded grey.
    Attached Files Attached Files
    <---------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
    Reply With QuoteRegister To Reply
  7. 12-12-2013, 09:00 AM #7
    sulbaran
    sulbaran is offline
    Registered User
    Join Date
    10-25-2012
    Location
    Baton Rouge
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Compare two date/time columns

    Thanks to all for the answer...

    Unfortunately the application used to collect the data, export the date/time as a text and I forgot to mention that; but in any case, after using formulas I still unable to identify the studies ordered before 19:00 done the next day. The goal in this case is audit how many cases are ordered one specific month before 19:00 but the study is done the next day and identify the reasons why this is happen.

    BTW Proton how do you eliminate the last two 00 for the data ? your format is mm/dd/yyyy, hh:mm but not the second ??, which I don't need either and wants to eliminate those last two digit.
    Thanks again...
    Last edited by sulbaran; 12-12-2013 at 10:11 AM.
    Reply With QuoteRegister To Reply
  8. 12-12-2013, 11:59 AM #8
    FDibbins
    FDibbins is offline
    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
    53,048

    Re: Compare two date/time columns

    I dont see any data in your sample that would "fail" the test, so using these 2 date/times in row 10...
    07/03/12 15:59:00
    07/04/12 09:51:05
    =(C10*1)<=INT(B10*1)+1+19/24 = TRUE

    But if I change the time to this...
    07/03/12 15:59:00
    07/04/12 19:51:05
    then it gives a FALSE answer, meaning this entry failed the test - the study was done after 1900 the next day
    Reply With QuoteRegister To Reply
  9. 12-12-2013, 12:26 PM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two date/time columns

    The dates in columns B and C are easily changed from text to real dates. Select column B then click on the Data tab, Text to columns, Delimited, click next until you get to the area where you can select data type click on the DATE and choose the date format then click FINISH.

    Repeat for column C.

    That is what I did in the workbook that I uploaded to you.
    Reply With QuoteRegister To Reply
  10. 12-12-2013, 01:32 PM #10
    sulbaran
    sulbaran is offline
    Registered User
    Join Date
    10-25-2012
    Location
    Baton Rouge
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Compare two date/time columns

    Could be relate to the format that application export the data to Excel but using that formula ( with or without change the cell format ) give me wrong answers = see the attached file with the formula -.

    I have been trying to find an easy fix to get the needed data, because what I usually do is identify line by line studies done the next day but had been ordered before the 19:00, mack as yellow and later do a manually count.
    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 12-12-2013, 01:49 PM #11
    FDibbins
    FDibbins is offline
    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
    53,048

    Re: Compare two date/time columns

    Based on your sample, try this, copied down. It picks up only those 2 that you highlighted...
    =AND((B2*1)<=INT(B2*1)+19/24,(C2*1)>=INT(B2*1)+19/24)
    Reply With QuoteRegister To Reply
  12. 12-12-2013, 02:59 PM #12
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two date/time columns

    Will this satisfy your needs. I have hidden columns D E F which are helpers. The counts for each order occur on the first row of the order. I moved your comments over to the right along with the column of TRUE FALSE.

    The formats for columns B and C were changed from TEXT using Text to Columns and choosing MDY as the date format.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 12-12-2013, 03:19 PM #13
    sulbaran
    sulbaran is offline
    Registered User
    Join Date
    10-25-2012
    Location
    Baton Rouge
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Compare two date/time columns

    I'm including an old complete set of data as I received from the application and I already identify the studies not done in the same day as ordered ( in yellow ), but must be ordered before 19:00 when we close.

    I still unable to use the formula suggested here to identify properly those in yellow.

    Thanks

    Sulbaran
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 12-12-2013, 03:22 PM #14
    FDibbins
    FDibbins is offline
    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
    53,048

    Re: Compare two date/time columns

    It seemed to work for me?
    see the attached...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 12-12-2013, 03:35 PM #15
    sulbaran
    sulbaran is offline
    Registered User
    Join Date
    10-25-2012
    Location
    Baton Rouge
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Compare two date/time columns

    When you apply the formula to the complete set of data, looks like the formula identify several "TRUE" as studies done the next day but those are studies done or the same day or the next day but were ordered after 19:00
    Sorry to put this complicate issue here but I can't find a solution myself , except do it manually and that is not such a problem for a small volume, but when dealing with big numbers on lines is a headache ...
    Reply With QuoteRegister To Reply
  16. 12-12-2013, 03:54 PM #16
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare two date/time columns

    Maybe this will do what you want...getting a bit confused but that is a normal state
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare two date columns
    By plk in forum Excel General
    Replies: 2
    Last Post: 10-25-2012, 05:47 AM
  2. How to compare columns on time
    By captrain_cruncy in forum Excel General
    Replies: 2
    Last Post: 07-19-2011, 10:48 AM
  3. Excel 2007 : Compare todays date with another date to show % time elapsed
    By LAB2007 in forum Excel General
    Replies: 1
    Last Post: 07-01-2010, 11:32 AM
  4. Compare two columns, text to date.
    By dygraham in forum Excel General
    Replies: 9
    Last Post: 06-18-2009, 02:39 PM
  5. Date and time compare
    By knowledge_619 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2005, 05:25 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1