Calculate how much time falls between set start and stop times
I have a fairly large amount of data involving time. I have a start time, a
stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
Tell us what cells you are using, then the solution can be geared
directly for you. Pete On Apr 9, 4:12*pm, Polly wrote: I have a fairly large amount of data involving time. *I have a start time, a stop time and elapsed time. *I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: *Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. *So the answer is (doing the math in my head ) is *57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
Pete:
Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
N2 is =MIN(J2,M$1)-MAX(I2,L$1)
O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
David,
I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
I used your original values and got 0:57 inside and 0:13 outside. I also
tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
David, you are fabulous - separating them out, it works, putting them back
together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
No, I've no idea why it's not working for you, Polly.
If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
David,
Did you try anything with your start time after 15:00? When I have that situation, my "in block" result is a negative number so when I subtract the elapsed time from it for the "outside block" result I'm getting the wrong answer. Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40 Min = 15:00, Max = 17:56 Inside Block = -176 Outside Block = 216 If I sum the Inside and Outside Blocks, then I get the right answer - but I shouldn't have to do that, right? I'm sorry, I've never used Min and Max before so I'm unfamiliar with their purpose. I truly do appreciate the education. Polly "David Biddulph" wrote: No, I've no idea why it's not working for you, Polly. If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
Yes, valid point.
Try changing N2 to =MAX(0,MIN(J2,M$1)-MAX(I2,L$1)) -- David Biddulph "Polly" wrote in message ... David, Did you try anything with your start time after 15:00? When I have that situation, my "in block" result is a negative number so when I subtract the elapsed time from it for the "outside block" result I'm getting the wrong answer. Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40 Min = 15:00, Max = 17:56 Inside Block = -176 Outside Block = 216 If I sum the Inside and Outside Blocks, then I get the right answer - but I shouldn't have to do that, right? I'm sorry, I've never used Min and Max before so I'm unfamiliar with their purpose. I truly do appreciate the education. Polly "David Biddulph" wrote: No, I've no idea why it's not working for you, Polly. If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
PERFECT!!!!
David - I don't know who you are, where you are, or what you do for a living, but you just saved me a BOATLOAD of time every month. Thank you so very much. Polly "David Biddulph" wrote: Yes, valid point. Try changing N2 to =MAX(0,MIN(J2,M$1)-MAX(I2,L$1)) -- David Biddulph "Polly" wrote in message ... David, Did you try anything with your start time after 15:00? When I have that situation, my "in block" result is a negative number so when I subtract the elapsed time from it for the "outside block" result I'm getting the wrong answer. Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40 Min = 15:00, Max = 17:56 Inside Block = -176 Outside Block = 216 If I sum the Inside and Outside Blocks, then I get the right answer - but I shouldn't have to do that, right? I'm sorry, I've never used Min and Max before so I'm unfamiliar with their purpose. I truly do appreciate the education. Polly "David Biddulph" wrote: No, I've no idea why it's not working for you, Polly. If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
One last question - I promise.
Can I combine this with an IF statement? For two of my 'blocks' I have different days of the week with different times. So what I want to say is if DOW = X (2, 3, 4, 5, 6) then use Y and Z for my Min and Max (M$1 and L$1 - obviously I will have different columns for the different days of the week, so it will look something like... if(A2(day of the week) = 2, original Min Max formulas, if(A2 = 3, new Min Max formula, if(A2=4, new Min Max formula, etc.)))) It seems like it ought to work, but I thought I'd ask before I spent time on it. Thanks again. "David Biddulph" wrote: Yes, valid point. Try changing N2 to =MAX(0,MIN(J2,M$1)-MAX(I2,L$1)) -- David Biddulph "Polly" wrote in message ... David, Did you try anything with your start time after 15:00? When I have that situation, my "in block" result is a negative number so when I subtract the elapsed time from it for the "outside block" result I'm getting the wrong answer. Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40 Min = 15:00, Max = 17:56 Inside Block = -176 Outside Block = 216 If I sum the Inside and Outside Blocks, then I get the right answer - but I shouldn't have to do that, right? I'm sorry, I've never used Min and Max before so I'm unfamiliar with their purpose. I truly do appreciate the education. Polly "David Biddulph" wrote: No, I've no idea why it's not working for you, Polly. If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
Yes, you could either use an IF statement as you suggest, or otherwise use
either CHOOSE or VLOOKUP functions. There should be a number of ways of getting the answer you're looking for. -- David Biddulph "Polly" wrote in message ... One last question - I promise. Can I combine this with an IF statement? For two of my 'blocks' I have different days of the week with different times. So what I want to say is if DOW = X (2, 3, 4, 5, 6) then use Y and Z for my Min and Max (M$1 and L$1 - obviously I will have different columns for the different days of the week, so it will look something like... if(A2(day of the week) = 2, original Min Max formulas, if(A2 = 3, new Min Max formula, if(A2=4, new Min Max formula, etc.)))) It seems like it ought to work, but I thought I'd ask before I spent time on it. Thanks again. "David Biddulph" wrote: Yes, valid point. Try changing N2 to =MAX(0,MIN(J2,M$1)-MAX(I2,L$1)) -- David Biddulph "Polly" wrote in message ... David, Did you try anything with your start time after 15:00? When I have that situation, my "in block" result is a negative number so when I subtract the elapsed time from it for the "outside block" result I'm getting the wrong answer. Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40 Min = 15:00, Max = 17:56 Inside Block = -176 Outside Block = 216 If I sum the Inside and Outside Blocks, then I get the right answer - but I shouldn't have to do that, right? I'm sorry, I've never used Min and Max before so I'm unfamiliar with their purpose. I truly do appreciate the education. Polly "David Biddulph" wrote: No, I've no idea why it's not working for you, Polly. If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
Glad to have been able to help.
-- David Biddulph "Polly" wrote in message ... PERFECT!!!! David - I don't know who you are, where you are, or what you do for a living, but you just saved me a BOATLOAD of time every month. Thank you so very much. Polly "David Biddulph" wrote: Yes, valid point. Try changing N2 to =MAX(0,MIN(J2,M$1)-MAX(I2,L$1)) -- David Biddulph "Polly" wrote in message ... David, Did you try anything with your start time after 15:00? When I have that situation, my "in block" result is a negative number so when I subtract the elapsed time from it for the "outside block" result I'm getting the wrong answer. Ex: Start time = 17:56, End Time = 18:35, Elapsed Time = 40 Min = 15:00, Max = 17:56 Inside Block = -176 Outside Block = 216 If I sum the Inside and Outside Blocks, then I get the right answer - but I shouldn't have to do that, right? I'm sorry, I've never used Min and Max before so I'm unfamiliar with their purpose. I truly do appreciate the education. Polly "David Biddulph" wrote: No, I've no idea why it's not working for you, Polly. If you are still struggling with the debugging it's sometimes worth temporarily reformatting cells to General or Number, rather than Time, to see what's going on. Excel times are in units of 1 day, so 12:00 is 0.5, 18:00 would be 0.75, 15:00 would be 0.625, etc. -- David Biddulph "Polly" wrote in message ... David, you are fabulous - separating them out, it works, putting them back together it does not work. I can leave them separate, but do you have any ideas as to why it wouldn't work putting them together? "David Biddulph" wrote: I used your original values and got 0:57 inside and 0:13 outside. I also tried other values, looking at both ends of the day, and got the right answers. Perhaps you've mistyped one or more of your values? To debug your data, try to separate out the terms. Look at =MIN(J2,M$1) and at =MAX(I2,L$1). -- David Biddulph "Polly" wrote in message ... David, I see where you are going with this, and I tried it, but I'm not getting the result I know I should be getting (always good to start with one you know the answer to). I think the problem is coming from the MIN MAX bit. Maybe I haven't explained the answer well enough. I have a block of time: 7:00 to 15:00, I have events that start after 7:00 and end before 15:00 - these are easy, total time falls within the block. If an event starts before 7:00 and ends before 15:00, then the bit before 7:00 is outside the block, and the bit between 7:00 and 15:00 is inside the block. If an event starts between 7:00 and 15:00 and ends after 15:00, then again, some of the time falls inside the block and some of the time outside the block. If the event starts after 15:00, then all of the time falls outside the block. When I used your formulas, all of my time is falling outside the block. I really appreciate your advice and hope you can help further. Polly "David Biddulph" wrote: N2 is =MIN(J2,M$1)-MAX(I2,L$1) O2 is =K2-N2 (assuming that K2 is =J2-I2) Multiply by 24*60 if you want to convert from Excel times to minutes. -- David Biddulph "Polly" wrote in message ... Pete: Start time is in I2 Stop time is in J2 Elapsed time is in K2 The start time for the calculation (07:00) is in L1 The stop time for the calculation (15:00) is in M1 The formula will appear in N2 and O2 respectively (time inside the block and time outside the block) So far I have an if statement that works if both the start and stop times fall between the block (0700-1500), I'm getting hung up where either the start time falls within the block but the end time doesn't, or vice versa). Polly "Pete_UK" wrote: Tell us what cells you are using, then the solution can be geared directly for you. Pete On Apr 9, 4:12 pm, Polly wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
"Polly" wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
I am using =Text(end time-start time,"[m]")
the answer is a number of minutes, if "[s]" is used the answer is seconds alsoa number. Similarly "[h]" can be used "Robin" wrote: "Polly" wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
I am using =Text(end time-start time,"[m]")
the answer is a number of minutes, if "[s]" is used the answer is seconds alsoa number. Similarly "[h]" can be used "Robin" wrote: "Polly" wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
Calculate how much time falls between set start and stop times
"Robin" wrote: I use =text(end time-start time,"[m]") this give a number in minutes, if seconds are required use "[s]" "Polly" wrote: I have a fairly large amount of data involving time. I have a start time, a stop time and elapsed time. I need to know how much of the elapsed time falls between set times elsewhere in the spreadsheet. Ex: Set times are 07:00 and 15:00 Start time = 14:03 End time = 15:13 Elapsed time = 70 The question I need to answer is, how much of the elapsed time falls between 7:00 and 15:00 and how much falls outside. So the answer is (doing the math in my head ) is 57 minutes inside and 13 minutes outside. But I need to do this for hundreds of rows of data and can't seem to come up with a formula that works. |
All times are GMT +1. The time now is 12:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com