Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
can excel calculate time increments in a formula? lmunzen Excel Worksheet Functions 2 September 11th 06 09:39 PM
formula to calculate time difference crossing midnight ditorejax Excel Worksheet Functions 3 August 17th 06 04:46 PM
calculate elapsed time between dates and times Jenna Excel Worksheet Functions 2 January 25th 06 04:39 PM
time sheet to calculate 2 different columns John Sullivan Excel Worksheet Functions 1 October 21st 05 06:48 AM
calculate negative or positve difference in time kpmoore Excel Discussion (Misc queries) 2 January 5th 05 01:35 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017