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

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

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, frozenfusion@webmail.co.za

2. ## RE: Calculate difference in time spanning a day, during office hours o

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!

"frozenfusion" wrote:

> 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, frozenfusion@webmail.co.za
>

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!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1