# [SOLVED] calculate difference in time spanning a day, during office hours o

1. ## [SOLVED] 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

I 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

3. ## Re: calculate difference in time spanning a day, during office hours o

You need an IF, IF statement
>> "Logical if"
>> if1 ("date out"-"date in")>=1,

>> "value if1 true"

If2("time in">time(17,0,0))
"value if2 true"
("time in">time(17,0,0))
"value if2 false"
(time(17,0,0)-"time in")+("time out"-time(7,30,0)

>> "value if1 false"
>> ("time out"-"time in")

Mike F

"frozenfusion" <frozenfusion@discussions.microsoft.com> wrote in message
news:38230274-B8E1-4B47-9C7B-7EAF549E75E5@microsoft.com...
>I 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

4. ## Re: calculate difference in time spanning a day, during office hours o

http://www.cpearson.com/excel/DateTimeWS.htm

Chip Pearson's page on this topic.

--
Regards,
Tom Ogilvy

"frozenfusion" <frozenfusion@discussions.microsoft.com> wrote in message
news:38230274-B8E1-4B47-9C7B-7EAF549E75E5@microsoft.com...
> I 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)<---produc
es #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)),(Q
19-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

5. ## Re: calculate difference in time spanning a day, during office hou

Thanks Tom and Mike
I'll have a go at it, and post back if it works

"Tom Ogilvy" wrote:

> http://www.cpearson.com/excel/DateTimeWS.htm
>
> Chip Pearson's page on this topic.
>
> --
> Regards,
> Tom Ogilvy
>
> "frozenfusion" <frozenfusion@discussions.microsoft.com> wrote in message
> news:38230274-B8E1-4B47-9C7B-7EAF549E75E5@microsoft.com...
> > I 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)<---produc
> es #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)),(Q
> 19-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

>
>

#### Thread Information

##### 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