Solved

Determining time difference between two lines.

Posted on 2011-09-20
1
307 Views
Last Modified: 2012-06-21
Hi.
I have a very large file which shows when a site looses power and when that site powers-up again.
Each site has two lines - the first line shows when the site looses power and the second line shows when the site powers up again. Column D contains the timestamp when these events occur.
Can you tell me please what the code is which will show, in seconds, the outage time in column E. (Note: bear in mind that the outage may extend over a number of days therefore it's important to note the date part of the timestamp too)
Many thanks.


A              B              C                                     D
3713      Site A      15/09/2011 02:19:27      Event 1 at 2011-09-15 02:19:27.0 : Power down
3713      Site A      15/09/2011 02:19:42      Event 2 at 2011-09-15 02:19:42.0 : Power up
5397      Site B      15/09/2011 02:35:32      Event 1 at 2011-09-15 02:35:32.0 : Power down
5397      Site B      15/09/2011 02:36:12      Event 2 at 2011-09-15 02:36:12.0 : Power up

0
Comment
Question by:Slainiae
1 Comment
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36567942
Column C looks like it has the time/date in a viable format - you can simple subtract and convert to get seconds, e.g. in E2

=(C2-C1)*86400

format E2 as number with no decimal places

To copy down leave e1 blank, select e1 and E2 and drag/fill down column

regards, barry
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
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…
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…

760 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

19 Experts available now in Live!

Get 1:1 Help Now