Solved

Comparing times in two cells with date and time

Posted on 2013-12-05
12
234 Views
Last Modified: 2013-12-05
I have a sheet with two columns one shows opened time and date the other shows closed time and date they both display the date and time in this format 2013/12/02 08:14:40.
I want to compare the two and report back on ones that are closed within 30 minutes or whatever time period I define but generally on the same day.
0
Comment
Question by:titanium123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 13

Expert Comment

by:akb
ID: 39699603
if you create a new column and subtract the first date/time from the last date/time you will end up with a new date/time. Format the new field as a number. The new field contains the number of days difference between the two date/times. Multiply the new field by 1440 to get minutes. You can then do what you need with the minutes.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39699748
I assume that your cells contain text that looks like date & time rather than a number that is formatted as date & time. If so, you can use a formula to trap time differences of 30 minutes or less for conditional formatting like this:
=AND(A1<>"",B1<>"",(DATEVALUE(B1)+TIMEVALUE(B1)-(DATEVALUE(A1)+TIMEVALUE(A1)))<=(TIME(0,30,0)+2^-17))

In the above formula, A1 is the starting date & time, B1 is the ending date & time. The 30 minute criteria is given by TIME(0,30,0). The bit with 2^-17 is to add a fraction of a second to the criteria so rounding errors don't render an exact 30 minute difference to fail the test.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39699758
If your cells contain date/time serial numbers that are formatted like yyyy/mm/dd hh:mm:ss, then the comparison formula is simpler:
=AND(A1<>"",B1<>"",(B1-A1)<=(TIME(0,30,0)+2^-17))

To test whether your cells contain date/time serial numbers or text, try changing the number format of one of the cells. If the appearance changes, then the cell contains a date/time serial number. If the appearance does not change, then the cell contains text.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:titanium123
ID: 39699844
when i change the format to general it displays the value as this 41608.70358 from a date and time of 30/11/2013  4:53:09 PM. I tried the formula byundt but get a #value error
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39699845
Hello Brad,

I'd expect your second formula to work even if the data is text. Normally any text value that would be converted to a date by DATEVALUE function will also be implicitly "co-erced" to a date by the subtraction in B1-A1

regards, barry
0
 
LVL 81

Expert Comment

by:byundt
ID: 39699860
titanium123,
I'm not reproducing your #VALUE! error with my second formula and date/time serial numbers as you found in your test. Could you please post a workbook that demonstrates the problem? It only needs to contain two times and a cell with the formula that doesn't work.

Brad


barryhoudini,
I thought my second formula would work with text, too. But my default dates are mm/dd/yyyy hh:mm:ss which results in #VALUE! error with text in the stated format.

Brad
0
 

Author Comment

by:titanium123
ID: 39699872
Hi I have attached a spreadsheet using both formulas which turn up the same #value error
Example.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39699886
The second formula does work, but you have to change the cell references to point to your data.  :-)
ExampleQ28311642.xlsx
0
 

Author Comment

by:titanium123
ID: 39699924
tried this one when i open the sheet i get a false error im using 2013 if it matters
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39699931
FALSE is the expected answer. The two date/times in question are more than 30 minutes apart. One is on 30 November and the other is on 2 December.
0
 

Author Comment

by:titanium123
ID: 39699935
Oh, of course. I forgot what my original question was : *)
0
 

Author Closing Comment

by:titanium123
ID: 39699980
thanks great solution
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question