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? |
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? |
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? |
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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 05:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com