Remember Me?

#1
January 30th 07, 01:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 162
IF statement to calculate time usage in specific time bands

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?

#2
January 30th 07, 01:50 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 10,593
IF statement to calculate time usage in specific time bands

=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)

"Daren" wrote in message
...
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?

#3
January 30th 07, 02:03 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,101
IF statement to calculate time usage in specific time bands

try

=((B8-A8+(B8<A8)))*24

assumes start in A8 end in B8
"Daren" wrote:

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?

#4
January 30th 07, 02:33 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 340
IF statement to calculate time usage in specific time bands

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.

On Jan 30, 1:50 pm, "Bob Phillips" wrote:
=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)

"Daren" wrote in ...

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?

#5
January 31st 07, 12:53 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 162
IF statement to calculate time usage in specific time bands

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.

"Mike" wrote:

try

=((B8-A8+(B8<A8)))*24

assumes start in A8 end in B8
"Daren" wrote:

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?

#6
January 31st 07, 01:21 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 10,593
IF statement to calculate time usage in specific time bands

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)

"Daren" wrote in message
...
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.

"Mike" wrote:

try

=((B8-A8+(B8<A8)))*24

assumes start in A8 end in B8
"Daren" wrote:

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?

#7
January 31st 07, 01:34 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 162
IF statement to calculate time usage in specific time bands

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?

"Bob Phillips" wrote:

=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)

"Daren" wrote in message
...
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?

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post lmunzen Excel Worksheet Functions 2 September 11th 06 09:39 PM ditorejax Excel Worksheet Functions 3 August 17th 06 04:46 PM Jenna Excel Worksheet Functions 2 January 25th 06 04:39 PM John Sullivan Excel Worksheet Functions 1 October 21st 05 06:48 AM kpmoore Excel Discussion (Misc queries) 2 January 5th 05 01:35 AM

All times are GMT +1. The time now is 02:53 PM.