Time Extraction

  • Thread starter Thread starter David M
  • Start date Start date
D

David M

I tried to explain this one yesterday but I don't think I
was clear so I will try again....

I have columns on my worksheet that calculates the
duration of a start time and end time then returns the
result in minutes...example:
Start Time End Time Minutes of outage
6:45 AM 7:00 AM 20
7:45 AM 8:20 AM 13

What I need to do id this...if I have a start time that
falls anywhere between 1:00AM and 2:00AM, lets say 1:00AM
to 2:40AM, I want to have excel say...wait the only part
of this time I need to figure is the 40 minutes....


Here is the reason: 1:00AM to 2:00AM is a window of time
that my users expect a system to be down..so if it is down
beyond that window...1:00AM to 2:40AM..I need to count
only the 40 minutes as actual minutes of downtime...right
now, I have to go back and check the data to ensure this
is done then I have to fudge the time so it calculates as
40 minutes...example: I change the start time to 2:00 to
2:40AM...I want to be able to somehow either extract the
40min or flag the timeframe and have excel do whatever to
get me that 40min as data is being entered into the
worksheet...

Hope this makes better sense....Any ideas??

Thanx so much

David
 
Hi

=B2-A2+(B2<A2)
and format as "hh:mm"
returns the result in time format, like 00:20 or 02:30

=B2-A2+(B2<A2)*(24*60)
and format as General or Number
returns the result as number of minutes, like 20 or 150
 
Hi

I'm assuming your boundaries (1:00 and 2:00) are in A1 and B1 resp. and your
actual Start and End times (1:00 and 2:40) are in A2 and B2 resp.
=((A2<$A$1)*($A$1-A2))+((B2>$B$1)*(B2-$B$1))
formatted as [mm] will give you the time before the first boundary your
Start Time is plus the time after the second boundary the End Time is!
In the above case, it will return 0:40
 
Well, here's my take on it.

Assuming,

Column A = Start Time
Column B = End Time
Column C = Minutes of Outage
Data starts in Row 2

Cell E1 = 1:00 AM
Cell E2 = 2:00 AM

Put this formula in C2 and copy down,

=IF(AND(A2>=$E$1,A2<=$E$2),(B2-A2+(B2<A2))-(TIME(2,0,0)-A2),B2-A2+(B2<A2))

and custom format as [m]

Hope this helps!
 
This looks to me like we are getting cloe...please llok at
my latest entry with the same title ..it gives more
information..

Thanx
 
Back
Top