Solved

Comparing times in two cells with date and time

Posted on 2013-12-05
12
212 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now