+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    frozenfusion
    Guest

    [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. #2
    frozenfusion
    Guest

    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. #3
    Mike Fogleman
    Guest

    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. #4
    Tom Ogilvy
    Guest

    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. #5
    frozenfusion
    Guest

    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

    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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