# Comparing times in two cells with date and time

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.
###### Who is Participating?

Commented:
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

Commented:
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

Commented:
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

Commented:
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 Commented:
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

Commented:

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

Commented:
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 Commented:
Hi I have attached a spreadsheet using both formulas which turn up the same #value error
Example.xlsx
0

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

Author Commented:
tried this one when i open the sheet i get a false error im using 2013 if it matters
0

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

Author Commented:
thanks great solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.