Solved

# Comparing times in two cells with date and time

Posted on 2013-12-05
219 Views
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
Question by:titanium123

LVL 13

Expert Comment

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

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

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

Author Comment

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

ID: 39699845

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

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.

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.

0

Author Comment

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

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

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

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

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

Author Closing Comment

ID: 39699980
thanks great solution
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

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 descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦