Calculate difference in time spanning a day, during office hours o

[Guest]

i'm trying to get the difference in times spanning a day during office hours
ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between
6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start
time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am

this is what i have so far, replace "date/time" with cell number

"Logical if"
if ("date out"-"date in")>=1,

"value if true"
(time(17,0,0)-"time in")+("time out"-time(7,30,0),

"value if false"
("time out"-"time in")

i can't figure out how to tell it if ("time in">time(17,0,0)) then it must
just
("time out"-time(7,30,0)) and not the whole value if true statement, and
still keep the whole thing...

=IF((P19-N19)>=1&(O19>TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)) <-----produces negative result #############

example of cells

date in time in Date Out time
out time Diff
(23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works

what i need, but still keeping the above working
23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00

if you can help, please mail, (e-mail address removed)

 

[Guest]

have also tried an if and statement

=IF(AND((P19-N19)>=1,(O19>TIME(17,0,0))),(Q19-TIME(7,30,0)),(Q19-O19))
IF(AND(P19-N19)>=1,(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)<---produces #Value!

i'm trying to get the difference in times spanning a day during office hours
ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between
6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start
time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am

this is what i have so far, replace "date/time" with cell number

"Logical if"
if ("date out"-"date in")>=1,

"value if true"
(time(17,0,0)-"time in")+("time out"-time(7,30,0),

"value if false"
("time out"-"time in")

i can't figure out how to tell it if ("time in">time(17,0,0)) then it must
just
("time out"-time(7,30,0)) and not the whole value if true statement, and
still keep the whole thing...

=IF((P19-N19)>=1&(O19>TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)) <-----produces negative result #############

example of cells

date in time in Date Out time
out time Diff
(23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works

what i need, but still keeping the above working
23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00

if you can help, please mail, (e-mail address removed)

have also tried an if and statement

=IF(AND((P19-N19)>=1,(O19>TIME(17,0,0))),(Q19-TIME(7,30,0)),(Q19-O19))
IF(AND(P19-N19)>=1,(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)<---produces #Value!