IF statement to calculate time usage in specific time bands

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data that shows stop and end points for certain procedures. For
example, something starts at 8:00 am and ends at 12:00 pm, which is a total
of 4 hours. What I need to know is how much of this time occurs in a
particular time band, say 7 am to 10 am, which would be three hours. I need
to figure out a way to write an IF statement that will capture this. The
problem I have so far is that the IF statement I've written works for many of
the time bands, but it's not picking up time usage for some reason in the 7
am - 1 pm range, when time is definitely being used. Is there some other way
I can write the IF statement or some way I should revise it so that it'll
pick up every calculation of time usage in a particular time band?
 
=MIN(B2,TIME(10,0,0))-MAX(A2,TIME(7,0,0))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Here is an amendment if A2 and B2 are both before or after the
interval 7-10am:

=MEDIAN(B2,C2,D2)-MEDIAN(A2,C2,D2)

where C2 and D2 are 7:00 and 10:00.
 
This still doesn't work. The problem is that I only want to calculate the
time used between 7 am and 1 pm. So if a procedure falls outside of this
time range, I don't want that time counted. For example, if a procedure runs
from 8 am to 2 pm, I only want to calculate the 1 pm - 8 am difference, which
would be 5 hours, and not capture any time outside of 1 pm. I tried what you
wrote below but it captures the entire time, and not the time I want only in
the range.
 
See the other responses to this thread.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I tried this also, but it doesn't work. I assumed the numeric values after
time would be 7:00, representing 7 am, and 13:00, representing 1 pm. Any
other suggestions?
 
Back
Top