Solved

Comparing times in two cells with date and time

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

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 80

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 80

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 80

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 80

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

744 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

12 Experts available now in Live!

Get 1:1 Help Now