OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calculate Total of cells based on Date/Time w/conditions

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
DeniseWO
General User
General User


Joined: 06 Mar 2011
Posts: 19

PostPosted: Tue Sep 20, 2011 8:30 am    Post subject: Calculate Total of cells based on Date/Time w/conditions Reply with quote

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
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Wed Sep 21, 2011 7:34 am    Post subject: Reply with quote

First I would make a column giving the previous Saturday at 00:00:
Code:
=7*int(A3/7)


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
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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