| View previous topic :: View next topic |
| Author |
Message |
DeniseWO
General User
Joined: 06 Mar 2011
Posts: 19
|
 Posted: Tue Sep 20, 2011 8:30 am Post subject: Calculate Total of cells based on Date/Time w/conditions |
 |
|
Hi All,
OK, need some help to create a formula to return a sum based on timing.
I am trying to determine how many rows occurred during the US Business hours, during a US (5:00am PT - 17:59pm PT) business week. how many occurred after hours (after 18:00pm PT) and how many occurred during a Fri (18:00 PT) to Sunday (23:59pm PT).
I have tried countif, SUMPRODUCT, and a conditional to no avail.
Example of data is below.
2 columns
Col 1 = Start Date and Time................Col 2 = End Date and Time
08/01/11 11:00...........................................08/02/11 06:00
08/03/11 14:30...........................................08/03/11 15:30
08/04/11 03:00...........................................08/04/11 09:00
08/05/11 17:00...........................................08/05/11 17:20
08/13/11 04:00...........................................08/13/11 05:00
08/14/11 04:00...........................................08/14/11 08:00
08/15/11 07:01...........................................08/16/11 08:01
08/18/11 01:00...........................................08/18/11 02:00
Thanks in advance for your help! |
|
| Back to top |
|
 |
davidh182
OOo Advocate
Joined: 01 Apr 2004
Posts: 413
|
| Posted: Wed Sep 21, 2011 7:34 am Post subject: |
|
|
First I would make a column giving the previous Saturday at 00:00:
Then subtract this from your start and end times to give days since Saturday. (in columns E+F). If your jobs are not too long none of these should be much greater than 7.
Next you need a column for each interval, for example
Mon 5:00 -> Mon 18:00 = 2.2083 -> 2.75
To find the overlap [how much of row 3 occured on Monday 5:00 -> 18:00] use
| Code: | | =MAX(MIN($F3,H$2)-MAX($E3,H$1),0) |
where E3 is the start time, F3 is the end time, H1 is the interval start (2.2083) and H2 is the interval end (2.75). Hopefully you won't need much more than a week of these.
[This formula will drag]
Finally add up the components. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
